Share via

Excel replacing tab name with #REF

Anonymous
2019-05-17T12:22:24+00:00

I have a workbook with a formula that references a figure in another workbook.

The formula is simply "='M:[Log V1-0.xlsx]Summary'!$C5"

This works, until I open Log V1-0.xlsx

Then Excel changes my formula to "='M:[Log V1-0.xlsx]#REF'!$C5"

This is happening in Excel for Office 365 on a couple of Windows 10 machines.

I don't have this problem on Excel 2010 in Windows 7.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2019-05-21T08:52:28+00:00

Hi Gordie,

As you said, it is an unexpected behavior. My idea depend on if the feature of cell reference still unworkable between different sheets? How about use Get Data option to get data from original workbook then create a cell reference between different sheets in a same workbook. 

Ensure you are using latest version of Excel, click File > Account > Update Options to update Excel to latest version(1905).

Regards,

Eric

Was this answer helpful?

1 person found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-05-21T09:20:15+00:00

    Hi Eric

    Initial tests of getting the data seem really promising. I will work on implementing this instead of my current approach, but yes I would say that looks like a solution.

    Thank you very much.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-05-21T08:32:17+00:00

    Hi Eric

    I can confirm the issue still occurred on the Win 10 machine when I created a pair of test workbooks.

    A colleague referred this to our IT contractors. No solution as yet.

    I have never seen Excel strip out a tab name before and replace it with #REF. I have only seen this happen when I have deleted rows (or tabs).

    I haven't removed tab called Summary, so it's baffling.

    Thank you for looking into this.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-05-20T07:42:39+00:00

    Hi Gordie,

    If you need further help, you can post your latest condition.

    Regards,

    Eric

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-05-17T16:18:39+00:00

    Hi Gordie,

    Please open both of workbooks and recrate the cell reference in a new sheet, see if there is any improvement, if the issue doesn't happen in new sheet, move your data to the new sheet.

    If the issue happen again, create a new workbook and create a cell reference again.

    If the issue still occur, which approve the original source data has been interrupted, please move you data source to a new workbook on your desktop and test again.

    Regards,

    Eric

    Was this answer helpful?

    0 comments