Share via

Broken formula when deleting a column.

Anonymous
2023-09-06T08:45:06+00:00

Hello everyone,

I am trying to make my Excel Spreadsheet continue to reference same cell on another Worksheet. When I delete the column which I do daily, it moves all data left. Which is not the issue.

I get a #!REF issue, but that column and cell is still there just new data. How do I continue to keep it references same Column, Row and Cell even if the Column deletes?

I hope I have explained this well enough.

Microsoft 365 and Office | Excel | For business | 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

  1. Anonymous
    2023-09-06T10:29:58+00:00

    =COUNTIF(INDIRECT("Sheet1!$D6:$D23"),"test")

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2023-09-06T09:45:51+00:00

    You may try indirect formula.

    =INDIRECT("I3")

    Image

    After delete column I. The reference is still I3.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-09-06T10:22:46+00:00

    How would I input INDIRECT into this phrase =COUNTIF(Sheet1!$D6:$D23,"TEST")

    I keep getting True/False.

    Thank you.

    0 comments