reference errors after moving location of cells

[SquashShriva1]

Hi, I am encountering a problem where I get reference error when I move cells around. In my workbook, there are two sheets, and cells in the second sheet are referencing cells in the first sheet. For example, cells B2:D2 on sheet 2 reference cells H2:J2 on sheet 1. (the formula for cells B2 on sheet 2 is =Sheet1!H2) However, when I move cells H2:J2 on sheet 1 a row down to row 3, on sheet two the values for B2:D2 get updated but cells B3:D3 now have a reference error. I would like for cells B2:D2 to also move down to cells B3:D3, and for cells B2:D2 to appear blank like the cells H2:J2 are on sheet 1. Is there any way to do this, will I have to use vba?

A simpler of version of my question is: if I move cell A1 on sheet 1 to A2, how do I get cell A1 to also move to A2 and make A1 equal to the new blank A

[jason.b75]

You need to move H2:J3 down, or right click H2:J2 and choose Insert > Shift down fromt the menu.

Moving H2:J2 down by dragging replaces H3:J3 so the original cells become non existant, hence the error.

[SquashShriva1]

When I move the cells in sheet 1, the cells in sheet 2 aren't moving, their values are just changing. Moving cells H2:J3 in sheet 1 one row down doesn't move cells B2:D3 in sheet 2, it just changes the values of cells B2:D3 to match H2:J3 and gives me a reference error from B4:D4

[jason.b75]

I might have misread your question, so to clarify the problem.

Try using the formula, =INDEX(Sheet1!H:H,ROW()) dragged to replace your existing formulas, does that do what you need?

[SquashShriva1]

Yeah I think that works, thank you!

[jason.b75]

You're welcome!

For reference, when you move cells by dragging, you 'replace' whatever you drag them over, which is why you get the errors on the second row of formulas.

The formulas in the first row followed the cells that you dragged, the formulas in the second row are still looking for the cells that were replaced by the ones that you dragged (which now no longer exist).

To force all of the formulas to 'follow' the cells that they refer to, you need to use 'Right click > Insert'
To force the formulas to look at the same place and not follow the cells, you need to use indirect methods (formulas that don't refer directly to the cell to be moved).

The method that I have shown you above is the one that I would use for the task, if for any reason that didn't work, then INDIRECT() would be an alternative, however the approach is less intuitive, especially if if it is not something that you are familiar with.