Tabs with the same name (sort of) as a cell

[Carty]

Hi. I am trying to write a formula in G6 of worksheet MAIN that uses the contents of a cell (G4) on worksheet "MAIN" as a reference to a worksheet with the same name (plus one word) as the cell


e.g. MAIN,G4 = monkey

MAIN,G6 would normally equal = ‘monkey baby’!E30

Unsurprisingly writing=’G4&”baby”’!E30 in G6 is returning a REF error


Can it be done this way or do I need to write some code?
Thanks
Paul

 

[JackDanIce]

Try:

=INDIRECT("'" & G4&" baby'!E30")

 

[Carty]

That works well, thank you

 

[JackDanIce]

You're welcome