Variable Table Array in Lookup Function
 

Not sure if that title makes sense but...

I'm using a formula

=HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)

At the moment it is looking for values in a table on Labour!

I have similar tables (in the same place ie B1:L2) on other
worksheets.

I want to chose which worksheet it looks at by creating a Drop Down
list in another cell on the same worksheet.

So basically I want the red bit in

HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)

to be dependant on what is in another cell.


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=568240

Variable Table Array in Lookup Function
 

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"matt_the_brum" wrote in message
news:matt_the_brum.2c0c14_1154681107.1697@excelfor um-nospam.com...
|
| Not sure if that title makes sense but...
|
| I'm using a formula
|
| =HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)
|
| At the moment it is looking for values in a table on Labour!
|
| I have similar tables (in the same place ie B1:L2) on other
| worksheets.
|
| I want to chose which worksheet it looks at by creating a Drop Down
| list in another cell on the same worksheet.
|
| So basically I want the red bit in
|
| HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)
|
| to be dependant on what is in another cell.
|
|
| --
| matt_the_brum
| ------------------------------------------------------------------------
| matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
| View this thread: http://www.excelforum.com/showthread...hreadid=568240
|

Variable Table Array in Lookup Function
 

Thanks. Had a look but not having much luck with the INDIRECT
function.

All I want is the Red writing in

=HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)

to equal whatever is in say cell A3.


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=568240

Variable Table Array in Lookup Function
 

I am trying somethig similar with VLOOKUP. If you get it working please
post the solution. Thanks


--
spxer
------------------------------------------------------------------------
spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025
View this thread: http://www.excelforum.com/showthread...hreadid=568240

Variable Table Array in Lookup Function
 

Likewise Spxer. Won't be working on it until next week now but will
post any progress.


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=568240

Variable Table Array in Lookup Function
 

Matt, try this:

=HLOOKUP(A10,INDIRECT(A3&"!$B$1:$L$2"),2,FALSE),FA LSE)

where A3 contains the sheet name. Ensure there are no spaces in any of
your sheet names, otherwise you will have to include apostrophes around
them in the formula.

Hope this helps.

Pete

matt_the_brum wrote:
Thanks. Had a look but not having much luck with the INDIRECT
function.

All I want is the Red writing in

=HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)

to equal whatever is in say cell A3.


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=568240

Variable Table Array in Lookup Function
 

Thanks Pete. Its working in the small formula, just got to try and fit
it into this,

=IF(ISNA(VLOOKUP(C14,Labour!$A$3:$L$12,HLOOKUP(A14 ,Labour!$B$1:$L$2,2,FALSE),FALSE)),,VLOOKUP(C14,La bour!$A$3:$L$12,HLOOKUP(A14,Labour!$B$1:$L$2,2,FAL SE),FALSE))

but it will have to wait until Monday now.


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=568240