Mirror cells even when rows are deleted/inserted

CTremblay

New Member
Joined
Mar 30, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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”).
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

CTremblay

New Member
Joined
Mar 30, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
I can't seem to figure out how to edit the original post?

However HI!

Im new here, so far every other question I have had I found googling and I joined here, because most of the answers I found, were found here. ;)

I am using EXCEL on mac 16.35. i also use the excel app on my Iphone and the online excel on the windows side.
 

CTremblay

New Member
Joined
Mar 30, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
So I tested a row using the indirect method and it kinda works. However not as intended. Basically all it seems to do is copy the next line onto the deleted spot so you end up with a duplicate line. Sooo now I’m back to square one...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,314
Messages
5,635,506
Members
416,861
Latest member
Breadnjam18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top