Index Match across multiple sheets

[n1lesh]

I was wondering if some one could help me with the following problem

i have peoples names and the total hours they work on different sheets going from sheet 1 to sheet 53

e.g

Week 1
A turner 40
S bridges 20
A bloggs 20

Week 2
J James 40
S bridges 40
S Jones 15

etc........

i was wondering if there was a formula that can look across the 53 sheets so when i put in a persons name it will give me the info from the sheets
e.g

Name: A Turner
(info from Week 1) 20
(info from Week 2) 40

Hope that makes sense and thankyou in advance

 

[louisH]

Hi,

To give a fully working formula we need more information about the architecture of your WorkBook.

This can help you :

To reference the same Range on multiple sheets, use ":".
For example if you want to sum every "A1" Cell on every sheet use :

=SUM('Week1:Week53'!A1)

Hope it helped

 

[n1lesh]

Hi,

To give a fully working formula we need more information about the architecture of your WorkBook.

This can help you :

To reference the same Range on multiple sheets, use ":".
For example if you want to sum every "A1" Cell on every sheet use :

=SUM('Week1:Week53'!A1)

Hope it helped

The only problem with this is that names are on different rows of each sheet

 

[louisH]

You could try to use the INDIRECT function.

=INDIRECT(sheetname & "A1")

Assuming the name of the guy is in Cell "D1" of your calculation sheet.
Write down your sheet names in Column A.

In column B use =SUMIFS(INDIRECT("'"&A1&"'"&"!B:B"), INDIRECT("'"&A1&"'"&"!A:A"), $D$1)

 

[n1lesh]

Thats brilliant for summing my values but what i need is the individual info from each sheet
so from my original post if i wanted all the info for the name S Bridges i want it displayed as followed

S bridges 20
40

i tried using index and match but i only know how to use on one work sheet not multiple ones
o

 

[louisH]

From what i understand you have 53 sheets named "Week 1", "Week 2", "Week 3", ..., "Week 52" ...
Each of your sheet has a column A with names, and a column B with numbers.

Then you want another sheet in which you type a name in a cell, say B1, and you get this :

The result of the formula should give you what you expect
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Sheet Name
[/TD]
[TD]S Bridges
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Week 1
[/TD]
[TD]=SUMIFS(INDIRECT("'"&A2&"'"&"!B:B"), INDIRECT("'"&A2&"'"&"!A:A"), $B$1)
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Week 2
[/TD]
[TD]=SUMIFS(INDIRECT("'"&A3&"'"&"!B:B"), INDIRECT("'"&A3&"'"&"!A:A"), $B$1)
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Week 3
[/TD]
[TD]=SUMIFS(INDIRECT("'"&A4&"'"&"!B:B"), INDIRECT("'"&A4&"'"&"!A:A"), $B$1)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[n1lesh]

From what i understand you have 53 sheets named "Week 1", "Week 2", "Week 3", ..., "Week 52" ...
Each of your sheet has a column A with names, and a column B with numbers.

Then you want another sheet in which you type a name in a cell, say B1, and you get this :

The result of the formula should give you what you expect
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sheet Name[/TD]
[TD]S Bridges[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Week 1[/TD]
[TD]=SUMIFS(INDIRECT("'"&A2&"'"&"!B:B"), INDIRECT("'"&A2&"'"&"!A:A"), $B$1)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Week 2[/TD]
[TD]=SUMIFS(INDIRECT("'"&A3&"'"&"!B:B"), INDIRECT("'"&A3&"'"&"!A:A"), $B$1)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Week 3[/TD]
[TD]=SUMIFS(INDIRECT("'"&A4&"'"&"!B:B"), INDIRECT("'"&A4&"'"&"!A:A"), $B$1)[/TD]
[/TR]
</tbody>[/TABLE]

Thank you just tried it was just what i was looking for
Much appreciated