Display another cell formula sheet name

[bakarken]

Hi guys

This might be an impossible question, but anything is worth a go.

I would like for one cell, say B1, to show the sheet name of a formula reference that, say A1, is linked to. Probably best if I show this in an example.

If cell A1 formula: ='Sheet2'!C3 then B1 would say 'Sheet2'

If cell A1 formula: ='Sheet3'!C7 then B1 would say 'Sheet3'

These are just examples of course, I basically want certain cells to show the sheet reference of another cell, to cross reference work automatically.

I would love to know a formula for this, but not interested in a VBA.

Can anyone help?

 

[AliGW]

Yep - easy peasy! Like this:

=MID(FORMULATEXT(A1),2,6)

 

[bakarken]

That resulted in '#NAME?', any ideas why?

 

[bakarken]

I've realised that is because I am using Excel 2010, which does not seem to have access to FORMULATEXT

 

[AliGW]

Ah, OK - it's useful to state which version you are using if not the most recent. In that case, you will probably need to use VBA, and I can't help you with that - sorry.

 

[AlanY]

try this

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

 

[bakarken]

Thank you, but unfortunately this only returned the sheet name of the sheet that the formula was posted from (in my examples, it returns 'Sheet1' instead of 'Sheet2')

 

[AlanY]

sorry, should read your request properly.