Formula with WorkSheet Name

[plans]

I have a value in cell named Cost of Sheet1

Cell F9 in Sheet2 holds the Sheet Name which is Sheet1

I have typed in this formula in Sheet2:

="='"&F9&"'!"&"Cost"
and what I am Getting is:
='Sheet1'!Cost
Why am I not getting the value of Cost?

Thanks

[FDibbins]

Because you are not including a cell ref?

Another way of doing this is...
=INDIRECT("'"&F9&"!'B4")

Or if Cost is a range name...
=cost

[Richard Buttrey]

If you have a named cell called Cost on Sheet1, why not just use

Please Login or Register  to view this content.

on sheet 2?

You seem to be over complicating this.

[plans]

Thank you FDibbins, the "INDIRECT" fixed the problem.

[plans]

Richard,
Thank you for the response;
I could not use =Cost because I have "Cost" in multiple worksheets, so I had to have the SheetName in front" 'SheetName'!Cost
Thanks

[FDibbins]

hmm you cannot have the same range name repeated within a workbook, so perhaps its not a range name after all?