How do i link a data from different sheet on a summary page automatically

p45cal · #3 01-18-2020, 08:32 AM

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.

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