Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-14-2020, 01:42 AM
Nurul Nurul is offline How do i link a data from different sheet on a summary page automatically Windows 8 How do i link a data from different sheet on a summary page automatically Office 2010
Novice
How do i link a data from different sheet on a summary page automatically
 
Join Date: Jan 2020
Location: Singapore
Posts: 1
Nurul is on a distinguished road
Send a message via AIM to Nurul
Default How do i link a data from different sheet on a summary page automatically

Hi all,

I am trying to tabulate a report of my colleagues spending hours on completing a task on a daily basis.
With different accounts for different colleague on daily basis, how do i make it easy for the summary to capture the report without having to link it manually.



i tried index and match, but cant seem to have it auto.

thank you in advance
Attached Files
File Type: xlsx January 2020.xlsx (46.5 KB, 5 views)
Reply With Quote
  #2  
Old 01-14-2020, 07:55 AM
ArviLaanemets ArviLaanemets is offline How do i link a data from different sheet on a summary page automatically Windows 8 How do i link a data from different sheet on a summary page automatically Office 2016
Expert
 
Join Date: May 2017
Posts: 565
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

In added example, all work registration is made in single table. You can get worktable for every consultant in given month from added report. Another report displays monthly summary report. Rest of tables are various registries which are needed to get various Data Validation Lists to work.


You can hide some register tables. And you can hide some calculated columns (colored brown) in data entry table and in consultant monthly report.


In summary monthly report, I couldn't understand from where some data was got, and what you tried to do. So the report is not finished.
Attached Files
File Type: xlsx WorkbookExample.xlsx (63.6 KB, 4 views)
Reply With Quote
  #3  
Old 01-18-2020, 08:32 AM
p45cal p45cal is offline How do i link a data from different sheet on a summary page automatically Windows 10 How do i link a data from different sheet on a summary page automatically Office 2019
Expert
 
Join Date: Apr 2014
Posts: 392
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

One formula in your Summary sheet, cell C5:
Code:
=INDEX(INDIRECT($A5 & "!$B$40:$AB$40"),MATCH(C$3,INDIRECT($A5 & "!$B$6:$AB$6"),0))
Copy down and across to column ZZ.
The names in column A of the Summary sheet must match exactly the names on the tab of each sheet.
Your formulae in cells AC5:AD8 differ slightly from the above by looking up the numbers in row 4 instead of the text in row 3, I've adhered to that,
AC5 formula:
Code:
=INDEX(INDIRECT($A5 & "!$B$40:$AB$40"),MATCH(AC$4,INDIRECT($A5 & "!$B$7:$AB$7"),0))
but I suspect there's no reason they can't be the same as the formulae in columns C to ZZ.


Streamlined your formula in C11 to:
=SUMPRODUCT(C$5:C$8,$B$5:$B$8)
copied across.

Please double check the results are what you're expecting in the attached.
Attached Files
File Type: xlsx msofficeforums44223January 2020.xlsx (35.6 KB, 2 views)
Reply With Quote
  #4  
Old 01-25-2020, 05:02 PM
GVaught GVaught is offline How do i link a data from different sheet on a summary page automatically Windows 10 How do i link a data from different sheet on a summary page automatically Office 2019
Novice
 
Join Date: Jan 2020
Location: Colorado
Posts: 12
GVaught is on a distinguished road
Default References

Are the sheets in the same workbook or are you wanting to summarize totals in a new sheet in the same workbook?

You can create a reference to the other sheet something like this:
=[@Amount]*$D$3

Where @Amount is the header column name on my other worksheet in the same workbook and $D$3 is the percentage rate that I'm multiplying by to put on my summary sheet.

You can also complete a direct reference in your summary by type equal symbol switch over to your other worksheet and select the field you wish to display, such a totals for a particular column.

Example: ='March Individual'!I6 where March Individual is the name of the sheet and I6 is the field I selected to store on my summary worksheet. You can also do this from different workbooks, however I have never completed such a task and I would imagine the workbooks should be in the same location so as not to break the link.

As the values change on your main worksheet your summary sheet gets automatically updated.

Hope this might help your question.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet mcronin Excel 1 05-09-2016 09:43 AM
How do i link a data from different sheet on a summary page automatically Automatically Organize data in a sheet differently (in order to create a dynamically updated graph) carlos_cs Excel 3 05-04-2016 08:44 AM
How to copy data automatically to particular sheet? nicholes Excel Programming 13 03-12-2014 01:05 AM
How do i link a data from different sheet on a summary page automatically Construct a summary sheet by summing up from one or more than one sheet. PRADEEPB270 Excel 1 11-04-2011 03:46 AM
How do i link a data from different sheet on a summary page automatically Link data from embedded excel sheet rwbarrett Word 1 05-27-2011 02:05 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 11:23 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft