| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
