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.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Killer" wrote:

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 he
http://www.nr1hockey.com/downloads/F...preadsheet.zip

my email is if you could correct the problem and send it to me.

Thanks again!

"Martin Fishlock" wrote:

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Killer" wrote:

Hi all I need your help getting this working as Im new to excel and just
know the basics what I have a datasheet of my football poll Im 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