Adding up multiple worksheets by name and totals

[Guest]

Hi all I need your help getting this working as I’m new to excel and just
know the basics what I have a datasheet of my football poll I’m running.

I have created 17 worksheets that add up to the full season.
I have one master sheet with all the breakdowns of each week from what
people have achieved in wins for each week

What I need is the correct formula for my master sheet that will lookup all
pages from W1 to W17, what it needs to lookup is the person name and his
total wins for that week and listed in on my master sheet. The master sheet
has breakdowns for each week and other useful information of what people have
achieved over the course of the season.

The person name and totals may not be in the same cell number on every
worksheet as this will change from week to week depending of there position
on wins over the course of the season.

Master Sheet:
W1 W2 W3 Total
1. John Dow 3 3 4 11
2. Joe Wing 4 2 3 9
3. Sam Fling 1 4 3 8

W1 Sheet
B5 AI5
Totals
10. John Dow 3
11. Sam Fling 1
12. Joe Wing 4

W2 Sheet
B5 AI5
Totals
4. Sam Fling 4
5. Joe Wing 2
15. John Dow 3

What it needs to do is lookup the name and totals and listed in on my master
sheet I hope this examples it clearly.

First person name will always start in column B5
Last person name will always be in column B37

The totals will always be in column AI5
The ending totals will always be in column AI37

I appreciate any help you can give me on this project.

Thanks ahead

 

[Guest]

This should work.

Put it in to the first cell on the master and copy down and across.

=VLOOKUP($B5,INDIRECT("'"&C$4&"'!$b$5:$ai$37"),34,FALSE)

It uses the header in the fourth row to find the worksheet.

 

[Guest]

Thanks for the help I gave you're idea a try and all I get is a REF error.
I have uploaded a simple of my spreadsheet if that would help.

It can be found here:
http://www.nr1hockey.com/downloads/FootballSpreadsheet.zip

my email is (e-mail address removed) if you could correct the problem and send it to me.

Thanks again!

 

[T. Valko]

Enter this formula in sheet Master cell D4:

=IF(ISNA(VLOOKUP($C4,INDIRECT(D$3&"!B5:AI37"),34,0)),"",VLOOKUP($C4,INDIRECT(D$3&"!B5:AI37"),34,0))

Copy across then down as needed.

Change the formula in Master cell D39 to:

=MAX(D436)

Copy across as needed.

Biff

 

[T. Valko]

=VLOOKUP($B5,INDIRECT("'"&C$4&"'!$b$5:$ai$37"),34,FALSE)

Tip/hint

Since Indirect is evaluating this portion as a TEXT representation of a
reference:

$b$5:$ai$37

The $ are not needed. They don't make the range absolute since the range is
a TEXT representation. The way Indirect works will make the range absolute.

Biff

 

[Guest]

Killer,

=IF(ISNA(VLOOKUP($C5,INDIRECT("'"&D$4&"'!$b$5:$ai$37"),34,FALSE)),"",VLOOKUP($C5,INDIRECT("'"&D$4&"'!$b$5:$ai$37"),34,FALSE)
)

this should work and it will solve the problem of the ref.