Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing VLookup from a variable number of sheets
On Worksheet "Record Sheet" cell C5 I need a formula which will do a VLookup
on a variable number of sheets (all of which have a name that begins with "Round " followed by a number) to search rows 8 through 23, column B (in the Round worksheets) for the value on the recordsheet in cell A5 and return the value of the cell in column BG on the Round sheets. The number of round sheets can vary between 4 and 25 or so but there are also additional sheets that I don't want searched after the Round worksheets. Programmatically, I would use a For Loop but I would like for it to update the RecordSheet when each of the Round worksheets are automatically updated rather than use a program. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing VLookup from a variable number of sheets
Hi Allen,
This may work for you if I understand your post. Got this from Peo Sjoblom in 2006. I'm sure there will be line wrapping since it is pretty long. It looks across eight worksheets in my example. If the value is not on the first it goes to the next and so on. Poe e-mailed me a two page explanation which I can almost, but not fully, understand how it works. =VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)0) ,0))&"'!A2:C200"),3,0) The look_up value is in cell A1 of the "Summary" sheet. The lookup_table is A2:C200 of each of the eight sheets. "MySheets" is a named range of a list of the eight worksheets. The 3 near the end returns column C of the look_up range, and the 0 at the end is for an exact match. This is an array formula and you use Ctrl + Shift + Enter to commit, Excel puts curly brackets { } around the formula. The formula would look like this if you did not use a named range for the worksheets. This one returns column B as you can see by the 2 near the end. Also array entered. =VLOOKUP(A1,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 2:A200"),A1)0),0))&"'!A2:C200"),2,0) I'm at a bit of a loss on how to account for from 4 to 25 worksheets unless you included all 25 in the named range. Post back if I can do more 'splaining... HTH Regards, Howard "Allen" wrote in message ... On Worksheet "Record Sheet" cell C5 I need a formula which will do a VLookup on a variable number of sheets (all of which have a name that begins with "Round " followed by a number) to search rows 8 through 23, column B (in the Round worksheets) for the value on the recordsheet in cell A5 and return the value of the cell in column BG on the Round sheets. The number of round sheets can vary between 4 and 25 or so but there are also additional sheets that I don't want searched after the Round worksheets. Programmatically, I would use a For Loop but I would like for it to update the RecordSheet when each of the Round worksheets are automatically updated rather than use a program. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing VLookup from a variable number of sheets
Hi again Allen,
Played around with the 4 to 25 worksheet issue and it seems I may have a solution for that if the aforementioned formula will work for you. Regards, Howard "Allen" wrote in message ... On Worksheet "Record Sheet" cell C5 I need a formula which will do a VLookup on a variable number of sheets (all of which have a name that begins with "Round " followed by a number) to search rows 8 through 23, column B (in the Round worksheets) for the value on the recordsheet in cell A5 and return the value of the cell in column BG on the Round sheets. The number of round sheets can vary between 4 and 25 or so but there are also additional sheets that I don't want searched after the Round worksheets. Programmatically, I would use a For Loop but I would like for it to update the RecordSheet when each of the Round worksheets are automatically updated rather than use a program. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing VLookup from a variable number of sheets
Oops. I left something off of my original explanation. I want it to do a
Vlookup on ALL of the sheets and SUM the resulting cells. Like I said, I'm not sure it's a formula problm since I have a variable number of cells. I may need to create some cells (25 of them to account for all possible issues) down further in the Record Sheet worksheet to bring in the values and then sum them from there. Is there a way to SUM a group of cels, some of which may contain errors because they are doing a VLookup that tries to access a non-existent worksheet? Sorry for the ommission. "L. Howard Kittle" wrote: Hi Allen, This may work for you if I understand your post. Got this from Peo Sjoblom in 2006. I'm sure there will be line wrapping since it is pretty long. It looks across eight worksheets in my example. If the value is not on the first it goes to the next and so on. Poe e-mailed me a two page explanation which I can almost, but not fully, understand how it works. =VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)0) ,0))&"'!A2:C200"),3,0) The look_up value is in cell A1 of the "Summary" sheet. The lookup_table is A2:C200 of each of the eight sheets. "MySheets" is a named range of a list of the eight worksheets. The 3 near the end returns column C of the look_up range, and the 0 at the end is for an exact match. This is an array formula and you use Ctrl + Shift + Enter to commit, Excel puts curly brackets { } around the formula. The formula would look like this if you did not use a named range for the worksheets. This one returns column B as you can see by the 2 near the end. Also array entered. =VLOOKUP(A1,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 2:A200"),A1)0),0))&"'!A2:C200"),2,0) I'm at a bit of a loss on how to account for from 4 to 25 worksheets unless you included all 25 in the named range. Post back if I can do more 'splaining... HTH Regards, Howard "Allen" wrote in message ... On Worksheet "Record Sheet" cell C5 I need a formula which will do a VLookup on a variable number of sheets (all of which have a name that begins with "Round " followed by a number) to search rows 8 through 23, column B (in the Round worksheets) for the value on the recordsheet in cell A5 and return the value of the cell in column BG on the Round sheets. The number of round sheets can vary between 4 and 25 or so but there are also additional sheets that I don't want searched after the Round worksheets. Programmatically, I would use a For Loop but I would like for it to update the RecordSheet when each of the Round worksheets are automatically updated rather than use a program. Thanks. . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing VLookup from a variable number of sheets
Well, from your last reply, I have to say "I don't know".
Peo's solution of looking across many sheets works great, but it has no provision to add multiple lookup's let alone even produce them. Sorry Regards, Howard "Allen" wrote in message ... On Worksheet "Record Sheet" cell C5 I need a formula which will do a VLookup on a variable number of sheets (all of which have a name that begins with "Round " followed by a number) to search rows 8 through 23, column B (in the Round worksheets) for the value on the recordsheet in cell A5 and return the value of the cell in column BG on the Round sheets. The number of round sheets can vary between 4 and 25 or so but there are also additional sheets that I don't want searched after the Round worksheets. Programmatically, I would use a For Loop but I would like for it to update the RecordSheet when each of the Round worksheets are automatically updated rather than use a program. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a variable number of rows | Excel Discussion (Misc queries) | |||
Summing a variable range | Excel Worksheet Functions | |||
Summing a Variable Number of Cells | Excel Discussion (Misc queries) | |||
Summing every nth row value based upon variable | Excel Worksheet Functions | |||
Summing a variable range of columns | Excel Worksheet Functions |