If statement searching a reference cell over a range of worksheets

[piggylumps]

Is there a way I can have a reference cell in one sheet where an IF statement will look at and then cross reference that with a range of other sheets?

Example, if cell a2= London then I need a formula that will oook for a sheet called London and return the value that's in B2 from the London sheet, BUT it needs to ignore all the other work sheets.

Does that make any sense?

 

[rdw72777]

Try:

=INDIRECT(A2&"!b2")

I know this works if your sheetname is one word (no spaces), don't think it works if your sheetnames have spaces.

 

[VoG]

Try

=INDIRECT("'"&A2&"'!B2")

 

[piggylumps]

Thank you, that's been driving me mental for hours now!