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

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

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

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

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

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

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

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

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

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