| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use HLOOKUP for different sheets depending on results from 2 cells
I have 2 cells with 3 possiable results each. Depending on the results
from both these cells, determines which sheet needs to be used to lookup the correct data on. I can get it to work when I specify what sheet to use, but the sheet is a variable and I cannot figure out how to get it to work. Thank |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use HLOOKUP for different sheets depending on results from 2 cells
How about providing some details!
I have 2 cells with 3 possiable results each. Which 2 cells? What are the 3 possible results? What sheet do you want to use in the lookup based on which result? What does your formula look like right now? Biff wrote in message oups.com... I have 2 cells with 3 possiable results each. Depending on the results from both these cells, determines which sheet needs to be used to lookup the correct data on. I can get it to work when I specify what sheet to use, but the sheet is a variable and I cannot figure out how to get it to work. Thank |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use HLOOKUP for different sheets depending on results from 2 cells
=HLOOKUP(D14, TROOP0!A1:R51, MATCH(F14, TROOP0!A2:A51, 0), TRUE)
The problem is that TROOP0 could be TROOP0, TROOP1, TROOP2, UNARMORED0, UNARMORED1, UNARMORED2, OR ARMORED0, ARMORED1, ARMORED2. This depends on what is chosen in 2 previous cells c14 and e14. In c13 the option is given to chose troop, armored, unarmored, and in e14 the option is given for 0, 1, 2 The sheets are numbered TROOP0, TROOP1 TROOP2 etc, all with varing data on them. I hope I'm being clear enough for you to get a mental picture thanks T. Valko wrote: How about providing some details! I have 2 cells with 3 possiable results each. Which 2 cells? What are the 3 possible results? What sheet do you want to use in the lookup based on which result? What does your formula look like right now? Biff wrote in message oups.com... I have 2 cells with 3 possiable results each. Depending on the results from both these cells, determines which sheet needs to be used to lookup the correct data on. I can get it to work when I specify what sheet to use, but the sheet is a variable and I cannot figure out how to get it to work. Thank |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use HLOOKUP for different sheets depending on results from 2 cells
I'm assuming the lookup tables on each sheet are in the exact same location.
=HLOOKUP(D14, INDIRECT(C14&E14&"!A1:R51"), MATCH(F14,INDIRECT(C14&E14&"!A2:A51"), 0), TRUE) Biff wrote in message oups.com... =HLOOKUP(D14, TROOP0!A1:R51, MATCH(F14, TROOP0!A2:A51, 0), TRUE) The problem is that TROOP0 could be TROOP0, TROOP1, TROOP2, UNARMORED0, UNARMORED1, UNARMORED2, OR ARMORED0, ARMORED1, ARMORED2. This depends on what is chosen in 2 previous cells c14 and e14. In c13 the option is given to chose troop, armored, unarmored, and in e14 the option is given for 0, 1, 2 The sheets are numbered TROOP0, TROOP1 TROOP2 etc, all with varing data on them. I hope I'm being clear enough for you to get a mental picture thanks T. Valko wrote: How about providing some details! I have 2 cells with 3 possiable results each. Which 2 cells? What are the 3 possible results? What sheet do you want to use in the lookup based on which result? What does your formula look like right now? Biff wrote in message oups.com... I have 2 cells with 3 possiable results each. Depending on the results from both these cells, determines which sheet needs to be used to lookup the correct data on. I can get it to work when I specify what sheet to use, but the sheet is a variable and I cannot figure out how to get it to work. Thank |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use HLOOKUP for different sheets depending on results from 2 cells
Well by entering that now I get a #REF! error. But I have data is both
the cells. Not sure what is going on with it T. Valko wrote: I'm assuming the lookup tables on each sheet are in the exact same location. =HLOOKUP(D14, INDIRECT(C14&E14&"!A1:R51"), MATCH(F14,INDIRECT(C14&E14&"!A2:A51"), 0), TRUE) Biff wrote in message oups.com... =HLOOKUP(D14, TROOP0!A1:R51, MATCH(F14, TROOP0!A2:A51, 0), TRUE) The problem is that TROOP0 could be TROOP0, TROOP1, TROOP2, UNARMORED0, UNARMORED1, UNARMORED2, OR ARMORED0, ARMORED1, ARMORED2. This depends on what is chosen in 2 previous cells c14 and e14. In c13 the option is given to chose troop, armored, unarmored, and in e14 the option is given for 0, 1, 2 The sheets are numbered TROOP0, TROOP1 TROOP2 etc, all with varing data on them. I hope I'm being clear enough for you to get a mental picture thanks T. Valko wrote: How about providing some details! I have 2 cells with 3 possiable results each. Which 2 cells? What are the 3 possible results? What sheet do you want to use in the lookup based on which result? What does your formula look like right now? Biff wrote in message oups.com... I have 2 cells with 3 possiable results each. Depending on the results from both these cells, determines which sheet needs to be used to lookup the correct data on. I can get it to work when I specify what sheet to use, but the sheet is a variable and I cannot figure out how to get it to work. Thank |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use HLOOKUP for different sheets depending on results from 2 cells
I figure I should try to give more info. This is an example of the
data in the varying sheets. This sheet being TROOP 0. But the data is different between all 9 sheets but its in this same format 200 300 400 500 600 1 100 100 100 100 100 2 100 100 100 100 100 3 100 100 100 100 100 4 100 100 100 100 100 5 100 100 100 100 100 6 100 100 100 100 100 7 100 100 100 100 100 8 100 100 100 100 100 9 96 97 100 100 100 Thanks again wrote: Well by entering that now I get a #REF! error. But I have data is both the cells. Not sure what is going on with it T. Valko wrote: I'm assuming the lookup tables on each sheet are in the exact same location. =HLOOKUP(D14, INDIRECT(C14&E14&"!A1:R51"), MATCH(F14,INDIRECT(C14&E14&"!A2:A51"), 0), TRUE) Biff wrote in message oups.com... =HLOOKUP(D14, TROOP0!A1:R51, MATCH(F14, TROOP0!A2:A51, 0), TRUE) The problem is that TROOP0 could be TROOP0, TROOP1, TROOP2, UNARMORED0, UNARMORED1, UNARMORED2, OR ARMORED0, ARMORED1, ARMORED2. This depends on what is chosen in 2 previous cells c14 and e14. In c13 the option is given to chose troop, armored, unarmored, and in e14 the option is given for 0, 1, 2 The sheets are numbered TROOP0, TROOP1 TROOP2 etc, all with varing data on them. I hope I'm being clear enough for you to get a mental picture thanks T. Valko wrote: How about providing some details! I have 2 cells with 3 possiable results each. Which 2 cells? What are the 3 possible results? What sheet do you want to use in the lookup based on which result? What does your formula look like right now? Biff wrote in message oups.com... I have 2 cells with 3 possiable results each. Depending on the results from both these cells, determines which sheet needs to be used to lookup the correct data on. I can get it to work when I specify what sheet to use, but the sheet is a variable and I cannot figure out how to get it to work. Thank |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use HLOOKUP for different sheets depending on results from 2 cells
The sheets are numbered TROOP0, TROOP1 TROOP2 etc
This sheet being TROOP 0 So, do the sheet names have spaces in them? If so, try this: =HLOOKUP(D14, INDIRECT("'"&C14&" "&E14&"'!A1:R51"), MATCH(F14,INDIRECT("'"&C14&" "&E14&"'!A2:A51"), 0)) Biff "tankervet" wrote in message ps.com... I figure I should try to give more info. This is an example of the data in the varying sheets. This sheet being TROOP 0. But the data is different between all 9 sheets but its in this same format 200 300 400 500 600 1 100 100 100 100 100 2 100 100 100 100 100 3 100 100 100 100 100 4 100 100 100 100 100 5 100 100 100 100 100 6 100 100 100 100 100 7 100 100 100 100 100 8 100 100 100 100 100 9 96 97 100 100 100 Thanks again wrote: Well by entering that now I get a #REF! error. But I have data is both the cells. Not sure what is going on with it T. Valko wrote: I'm assuming the lookup tables on each sheet are in the exact same location. =HLOOKUP(D14, INDIRECT(C14&E14&"!A1:R51"), MATCH(F14,INDIRECT(C14&E14&"!A2:A51"), 0), TRUE) Biff wrote in message oups.com... =HLOOKUP(D14, TROOP0!A1:R51, MATCH(F14, TROOP0!A2:A51, 0), TRUE) The problem is that TROOP0 could be TROOP0, TROOP1, TROOP2, UNARMORED0, UNARMORED1, UNARMORED2, OR ARMORED0, ARMORED1, ARMORED2. This depends on what is chosen in 2 previous cells c14 and e14. In c13 the option is given to chose troop, armored, unarmored, and in e14 the option is given for 0, 1, 2 The sheets are numbered TROOP0, TROOP1 TROOP2 etc, all with varing data on them. I hope I'm being clear enough for you to get a mental picture thanks T. Valko wrote: How about providing some details! I have 2 cells with 3 possiable results each. Which 2 cells? What are the 3 possible results? What sheet do you want to use in the lookup based on which result? What does your formula look like right now? Biff wrote in message oups.com... I have 2 cells with 3 possiable results each. Depending on the results from both these cells, determines which sheet needs to be used to lookup the correct data on. I can get it to work when I specify what sheet to use, but the sheet is a variable and I cannot figure out how to get it to work. Thank |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use HLOOKUP for different sheets depending on results from 2 cells
I have tried it both ways with space and without and its the same. For
some reason it give me the REF error on "indirect" in the formula. T. Valko wrote: The sheets are numbered TROOP0, TROOP1 TROOP2 etc This sheet being TROOP 0 So, do the sheet names have spaces in them? If so, try this: =HLOOKUP(D14, INDIRECT("'"&C14&" "&E14&"'!A1:R51"), MATCH(F14,INDIRECT("'"&C14&" "&E14&"'!A2:A51"), 0)) Biff "tankervet" wrote in message ps.com... I figure I should try to give more info. This is an example of the data in the varying sheets. This sheet being TROOP 0. But the data is different between all 9 sheets but its in this same format 200 300 400 500 600 1 100 100 100 100 100 2 100 100 100 100 100 3 100 100 100 100 100 4 100 100 100 100 100 5 100 100 100 100 100 6 100 100 100 100 100 7 100 100 100 100 100 8 100 100 100 100 100 9 96 97 100 100 100 Thanks again wrote: Well by entering that now I get a #REF! error. But I have data is both the cells. Not sure what is going on with it T. Valko wrote: I'm assuming the lookup tables on each sheet are in the exact same location. =HLOOKUP(D14, INDIRECT(C14&E14&"!A1:R51"), MATCH(F14,INDIRECT(C14&E14&"!A2:A51"), 0), TRUE) Biff wrote in message oups.com... =HLOOKUP(D14, TROOP0!A1:R51, MATCH(F14, TROOP0!A2:A51, 0), TRUE) The problem is that TROOP0 could be TROOP0, TROOP1, TROOP2, UNARMORED0, UNARMORED1, UNARMORED2, OR ARMORED0, ARMORED1, ARMORED2. This depends on what is chosen in 2 previous cells c14 and e14. In c13 the option is given to chose troop, armored, unarmored, and in e14 the option is given for 0, 1, 2 The sheets are numbered TROOP0, TROOP1 TROOP2 etc, all with varing data on them. I hope I'm being clear enough for you to get a mental picture thanks T. Valko wrote: How about providing some details! I have 2 cells with 3 possiable results each. Which 2 cells? What are the 3 possible results? What sheet do you want to use in the lookup based on which result? What does your formula look like right now? Biff wrote in message oups.com... I have 2 cells with 3 possiable results each. Depending on the results from both these cells, determines which sheet needs to be used to lookup the correct data on. I can get it to work when I specify what sheet to use, but the sheet is a variable and I cannot figure out how to get it to work. Thank |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use HLOOKUP for different sheets depending on results from 2 cells
Can you send me a copy of this file? I see you have a military email
address. I'm an AF vet. If so, I'm at: xl can help at comcast period net Remove "can" and change the obvious. Biff "tankervet" wrote in message oups.com... I have tried it both ways with space and without and its the same. For some reason it give me the REF error on "indirect" in the formula. T. Valko wrote: The sheets are numbered TROOP0, TROOP1 TROOP2 etc This sheet being TROOP 0 So, do the sheet names have spaces in them? If so, try this: =HLOOKUP(D14, INDIRECT("'"&C14&" "&E14&"'!A1:R51"), MATCH(F14,INDIRECT("'"&C14&" "&E14&"'!A2:A51"), 0)) Biff "tankervet" wrote in message ps.com... I figure I should try to give more info. This is an example of the data in the varying sheets. This sheet being TROOP 0. But the data is different between all 9 sheets but its in this same format 200 300 400 500 600 1 100 100 100 100 100 2 100 100 100 100 100 3 100 100 100 100 100 4 100 100 100 100 100 5 100 100 100 100 100 6 100 100 100 100 100 7 100 100 100 100 100 8 100 100 100 100 100 9 96 97 100 100 100 Thanks again wrote: Well by entering that now I get a #REF! error. But I have data is both the cells. Not sure what is going on with it T. Valko wrote: I'm assuming the lookup tables on each sheet are in the exact same location. =HLOOKUP(D14, INDIRECT(C14&E14&"!A1:R51"), MATCH(F14,INDIRECT(C14&E14&"!A2:A51"), 0), TRUE) Biff wrote in message oups.com... =HLOOKUP(D14, TROOP0!A1:R51, MATCH(F14, TROOP0!A2:A51, 0), TRUE) The problem is that TROOP0 could be TROOP0, TROOP1, TROOP2, UNARMORED0, UNARMORED1, UNARMORED2, OR ARMORED0, ARMORED1, ARMORED2. This depends on what is chosen in 2 previous cells c14 and e14. In c13 the option is given to chose troop, armored, unarmored, and in e14 the option is given for 0, 1, 2 The sheets are numbered TROOP0, TROOP1 TROOP2 etc, all with varing data on them. I hope I'm being clear enough for you to get a mental picture thanks T. Valko wrote: How about providing some details! I have 2 cells with 3 possiable results each. Which 2 cells? What are the 3 possible results? What sheet do you want to use in the lookup based on which result? What does your formula look like right now? Biff wrote in message oups.com... I have 2 cells with 3 possiable results each. Depending on the results from both these cells, determines which sheet needs to be used to lookup the correct data on. I can get it to work when I specify what sheet to use, but the sheet is a variable and I cannot figure out how to get it to work. Thank |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use HLOOKUP for different sheets depending on results from 2 cells
File sent and help greatly appreciated!
T. Valko wrote: Can you send me a copy of this file? I see you have a military email address. I'm an AF vet. If so, I'm at: xl can help at comcast period net Remove "can" and change the obvious. Biff "tankervet" wrote in message oups.com... I have tried it both ways with space and without and its the same. For some reason it give me the REF error on "indirect" in the formula. T. Valko wrote: The sheets are numbered TROOP0, TROOP1 TROOP2 etc This sheet being TROOP 0 So, do the sheet names have spaces in them? If so, try this: =HLOOKUP(D14, INDIRECT("'"&C14&" "&E14&"'!A1:R51"), MATCH(F14,INDIRECT("'"&C14&" "&E14&"'!A2:A51"), 0)) Biff "tankervet" wrote in message ps.com... I figure I should try to give more info. This is an example of the data in the varying sheets. This sheet being TROOP 0. But the data is different between all 9 sheets but its in this same format 200 300 400 500 600 1 100 100 100 100 100 2 100 100 100 100 100 3 100 100 100 100 100 4 100 100 100 100 100 5 100 100 100 100 100 6 100 100 100 100 100 7 100 100 100 100 100 8 100 100 100 100 100 9 96 97 100 100 100 Thanks again wrote: Well by entering that now I get a #REF! error. But I have data is both the cells. Not sure what is going on with it T. Valko wrote: I'm assuming the lookup tables on each sheet are in the exact same location. =HLOOKUP(D14, INDIRECT(C14&E14&"!A1:R51"), MATCH(F14,INDIRECT(C14&E14&"!A2:A51"), 0), TRUE) Biff wrote in message oups.com... =HLOOKUP(D14, TROOP0!A1:R51, MATCH(F14, TROOP0!A2:A51, 0), TRUE) The problem is that TROOP0 could be TROOP0, TROOP1, TROOP2, UNARMORED0, UNARMORED1, UNARMORED2, OR ARMORED0, ARMORED1, ARMORED2. This depends on what is chosen in 2 previous cells c14 and e14. In c13 the option is given to chose troop, armored, unarmored, and in e14 the option is given for 0, 1, 2 The sheets are numbered TROOP0, TROOP1 TROOP2 etc, all with varing data on them. I hope I'm being clear enough for you to get a mental picture thanks T. Valko wrote: How about providing some details! I have 2 cells with 3 possiable results each. Which 2 cells? What are the 3 possible results? What sheet do you want to use in the lookup based on which result? What does your formula look like right now? Biff wrote in message oups.com... I have 2 cells with 3 possiable results each. Depending on the results from both these cells, determines which sheet needs to be used to lookup the correct data on. I can get it to work when I specify what sheet to use, but the sheet is a variable and I cannot figure out how to get it to work. Thank |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formulae to look at 52 sheets &count if 2 cells have data entered | Excel Discussion (Misc queries) | |||
| formulae to look at 52 sheets &count if 2 cells have data entered | Excel Discussion (Misc queries) | |||
| How to populate sheets from a specified set of cells.. | Excel Worksheet Functions | |||
| How do I protect formula cells on multiple sheets? | Excel Worksheet Functions | |||
| How to get excel cells to change colors depending on value | Excel Worksheet Functions | |||
Linear Mode
