"#REF! error" when deleting rows in source sheet

[SD-2023]

Hi,

I'm copying information from one sheet (Sheet 1) to another (Sheet 2) in excel in order to streamline and tidy up the information. Sheet 1 comes with a very large number of excess rows, which I've used a macro to remove so they aren't carried across into Sheet 2. However, this is making the formulas in Sheet 2 come up with the #REF! error. I've looked up and tested solutions (including using INDEX and $), but none so far have worked in my spreadsheet. How do I make the formulas take from the next row after the deleted ones without having to reset the formulas every time?

The most problematic formula I'm using is:

=IF(Sheet3!M3<>"-",Sheet3!M3&""&Sheet3!CK3,IF(Sheet3!AA3<>"-",Sheet3!AA3,IF(Sheet3!AG3<>"",Sheet3!AG3,IF(Sheet3!AQ3<>"-",Sheet3!AQ3,"no value"))))

Hopefully any solution that works for this will work for the other formulas as well. I am open to using macros in this scenario as well, I've just had no luck finding one that will help me sort more than 100 columns worth of information into 30 in a different order

Thank you in advance :)

[#2]

@SD-2023 Can't really imagine why you would use macro's to do data cleaning and transformations. Power Query is the right tool for that. You mention Sheet1 and Sheet2 but the formula refers to a Sheet3.  How does Sheet3 relate to Sheet1 and Sheet2?

Now, when you directly refer to a cell, let's say M3, as soon as your macro deletes that cell or the entire row 3 or the entire column M, you will get #REF!. There's nothing you can do about that. You would have to work with INDEX and MATCH functions where you look for matching row and column headers in a certain range. Or you could work out an OFFSET function where you look for the cell 2 rows down and 12 columns right from cell A1, which would be M3.

However, Excel is smart enough to automatically update a direct cell reference the when you delete or insert rows/columns above/to the left of the cell.