- Mar 30, 2020
- Office Version
basically I have easy to read and modify values on sheet 1. Then on sheet 2 I have references to sheet1 (=sheet1!A1) in proper arrangement, with additional formatting mixed in. The problem is obvious when someone inserts, cuts, or deletes rows, on sheet1 you get a #ref error in sheet 2. I believe I can fix this using ”=indirect(“sheet1!A1”)” however I can’t drag it out and have it copy but change to the relevant cells, it just copies ”=indirect(“sheet1!A1”)” to the cell that should be “=indirect(“sheet1!B1”)”. Right now I have all cells with the proper cell reference. So what I think I need is a VBA macro that allows me to select a range of cells and change all the existing cell references to an indirect formula with the corresponding cell reference For example Select sheet2 A12:N1512. run macro. sheet 2 A12 Originally read “=sheet1!A3” and needs to change to ”=indirect(“sheet1!A3”)”. sheet 2 c21 originally read “=sheet1!C12” and needs to change to “=indirect(“sheet1!C12”).