Share this content
0
881

Remove the workbook name from a cell reference

How to permanently remove the workbook name from a cell reference

Didn't find your answer?

Search AccountingWEB

I have a workbook which has suddenly and mysteriously showing the workbook name in all the cell references. e.g. ='workbook name.xlsm'!$C$. This phenomena is present through all the sheets in this large workbook.

I wish to remove the workbook name from the reference. I can edit / delete the workbook name (using find/replace) and the cell still works fine, but when I save, close and reopen the workbook name returns. I have never encountered this before. Does anyone have a solution to ensuring the workbook name is permanently removed.

You may rightly ask why do I need to remove the workbook name if the results still pull through - very long story - suffice to say removal of workbook name will save me a lot of time.

Many thanks

Replies

Please login or register to join the discussion.

avatar
By SXGuy
11th Oct 2018 08:08

Try saving as another format, then open that and convert back.

Which version of excel are you using? That may help.

Thanks (0)
avatar
By mung1
to SXGuy
11th Oct 2018 18:59

Thank you SXGuy. By the time I read your advice we had already tried that. We saved it as xls, then made the formula edits, then saved it as a xlsm - and that worked. So you were on the money. Thank you.

Thanks (0)
avatar
11th Oct 2018 14:49

Very odd. It is the reappearance after saving and reopening which is particularly perplexing.

I see that it is an.xlsm file. Are there actually macros that are being used and, if so, could they be creating the issue? Can you try saving as a .xlsx file and see if the problem continues?

What does Data - Edit links show?

Thanks (0)
avatar
By mung1
to paulwakefield1
11th Oct 2018 19:00

Thank you Paul. By the time I read your advice this evening we had already tried that. We saved it as xls, then made the formula edits, then saved it as a xlsm - and that worked. So you were on the money. Thank you.

Thanks (0)
Share this content