selecting a sheet from cell contents for a formula

[Kade]

Hi All

I think I am missing the spokes to the wheel here

How can I reference a sheet name from a cell contents

book1
	A	B	C	D
1	ID	SheetName	Result
2	1005	Greensheet	=VLOOKUP(A2,Greensheet!$B$20:$C$100,2,FALSE)
3	1006	Bluesheet	VLOOKUP(A3,SheetNameFromB3!$B$20:$C$100,2,FALSE)
4	1008	Greensheet
5	1012	Redsheet
Sheet1

WHat I want is in C3

tia kd

(BTW I cannot use TREED option as addins and extensions cannot be installed )

 

[TryingToLearn]

try:

=VLOOKUP(A3,$c3 & "!"&B3!$B$20:$C$100,2,FALSE)

 

[kieran]

kd
Something like
=VLOOKUP(A3,INDIRECT(B3 & "!$B$20:$C$100",2,FALSE) should work

 

[Kade]

Kieran, can't get it work did you mean


=VLOOKUP(A3,INDIRECT(B3 & "!")$B$20:$C$100",2,FALSE)

I have tried so many versions of this but still I can't get it right.

kd

 

[Kade]

Got it kieran many thanks

kd

 

[kieran]

Kade,

sorry - I should have tested.

Hopefully you ended up with something like
=VLOOKUP(A3,INDIRECT(B3 & "!$B$20:$C$100"),2,FALSE)