|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
||||
|
||||
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)) 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)) 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. |
#4
|
|||
|
|||
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. |
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 |
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 |
Construct a summary sheet by summing up from one or more than one sheet. | PRADEEPB270 | Excel | 1 | 11-04-2011 03:46 AM |
Link data from embedded excel sheet | rwbarrett | Word | 1 | 05-27-2011 02:05 AM |