Adding up multiple worksheets by name and totals
 

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

Adding up multiple worksheets by name and totals
 

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

Adding up multiple worksheets by name and totals
 

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

Adding up multiple worksheets by name and totals
 

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(D4:D36)

Copy across as needed.

Biff

"Killer" wrote in message
...
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 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

Adding up multiple worksheets by name and totals
 

=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

"Martin Fishlock" wrote in message
...
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 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

Adding up multiple worksheets by name and totals
 

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

Adding up multiple worksheets by name and totals
 

Thanks guys the help is much appreciated.

"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