Hi.

If I have a value that I want to use to look up in a table but not
necessarily in the same table each time. Is it possible to set the Lookup
Range to be dynamic so that I can specify which table is being looked at by
changing a value in a combo box rather than having to change the formula in
the result cell?

Cheers.

Yes, you can do this.

How many items are in your combo box?

It'd be easier to use a data validation drop down list.

If you link the combo box to a cell the *number* of the item that is
selected is returned to the linked cell. You'd then have to parse what item
corresponds to the number.

If you use a DV drop down list you don't have to go through that extra step.
For example:

A1 = data validation drop down list. The selections are named ranges to use
in a VLOOKUP formula.

Then your lookup formula would be:

=VLOOKUP(lookup_value,INDIRECT(A1),col_index_numbe r,range_lookup)

Biff

"Ben Bruce" wrote in message
...
Hi.

If I have a value that I want to use to look up in a table but not
necessarily in the same table each time. Is it possible to set the Lookup
Range to be dynamic so that I can specify which table is being looked at
by
changing a value in a combo box rather than having to change the formula
in
the result cell?

Cheers.

NB:

If your named ranges are *dynamic ranges* then a different formula will be
needed. If that's the case this adds that extra step back into the process
and you might as well just stick with a combo box.

Biff

"T. Valko" wrote in message
...
Yes, you can do this.

How many items are in your combo box?

It'd be easier to use a data validation drop down list.

If you link the combo box to a cell the *number* of the item that is
selected is returned to the linked cell. You'd then have to parse what
item corresponds to the number.

If you use a DV drop down list you don't have to go through that extra
step. For example:

A1 = data validation drop down list. The selections are named ranges to
use in a VLOOKUP formula.

Then your lookup formula would be:

=VLOOKUP(lookup_value,INDIRECT(A1),col_index_numbe r,range_lookup)

Biff

"Ben Bruce" wrote in message
...
Hi.

If I have a value that I want to use to look up in a table but not
necessarily in the same table each time. Is it possible to set the Lookup
Range to be dynamic so that I can specify which table is being looked at
by
changing a value in a combo box rather than having to change the formula
in
the result cell?

Cheers.

Hi


"T. Valko" wrote in message
...
NB:

If your named ranges are *dynamic ranges* then a different formula will be
needed. If that's the case this adds that extra step back into the process
and you might as well just stick with a combo box.


Not at all!

An example:
=CHOOSE(MATCH(A1,{Value1;Value2;Value3;...},0),Ran ge1,Range2,Range3,...)



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Not at all!

What I meant was:

If your named ranges are *dynamic ranges* then a different formula will be
needed.

=VLOOKUP(lookup_value,INDIRECT(A1),col_index_numbe r,range_lookup)

Will not work with a dynamic range and therefore a different formula would
be needed.

Biff

"Arvi Laanemets" wrote in message
...
Hi


"T. Valko" wrote in message
...
NB:

If your named ranges are *dynamic ranges* then a different formula will
be needed. If that's the case this adds that extra step back into the
process and you might as well just stick with a combo box.


Not at all!

An example:
=CHOOSE(MATCH(A1,{Value1;Value2;Value3;...},0),Ran ge1,Range2,Range3,...)



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Hi

To use with VLOOKUP, I'd create an additional named range, i.e.

- You have some named ranges Name1, Name2, ...
- You create a named range, p.e. LookupRange, which returns one of those,
depending on value in cell SomeSheet!$A$1
- Your lookup formula will be:
=VLOOKUP(lookup_value,LookupRange,col_index_number ,range_lookup)

A bonus is, that whenever you need to change rules for selecting named
ranges, you have to change them once - for LookupRange - instead editing
every formula in workbook.



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"T. Valko" wrote in message
...
Not at all!

What I meant was:

If your named ranges are *dynamic ranges* then a different formula will be
needed.

=VLOOKUP(lookup_value,INDIRECT(A1),col_index_numbe r,range_lookup)

Will not work with a dynamic range and therefore a different formula would
be needed.

Biff

"Arvi Laanemets" wrote in message
...
Hi


"T. Valko" wrote in message
...
NB:

If your named ranges are *dynamic ranges* then a different formula will
be needed. If that's the case this adds that extra step back into the
process and you might as well just stick with a combo box.


Not at all!

An example:
=CHOOSE(MATCH(A1,{Value1;Value2;Value3;...},0),Ran ge1,Range2,Range3,...)



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )