0

I am referencing a range of cells in a first sheet, to build a second sheet. Often I add rows in the middle of the first sheet. In the second sheet that is referencing the first, there is a skip in the cell number where I have added a row.

SHEET 1: Contains my main list, that is updated

Sheet 1

A new row is added (A3) to SHEET 1:

Sheet 2

SHEET 2: references Sheet 1 and pulls through the rows

However, you can see that where row 3 should contain the added row 'Rachael', it instead has shifted down to Sheet1!A4 and missed A3 out all together.

How can I fix this?

enter image description here

pnuts
  • 58,317
  • 11
  • 87
  • 139
Takeshi Patterson
  • 1,207
  • 6
  • 15
  • 29
  • Possible duplicate of [Get content of a cell given the row and column numbers](http://stackoverflow.com/questions/4813888/get-content-of-a-cell-given-the-row-and-column-numbers) – ergonaut Oct 16 '15 at 12:52

3 Answers3

1

Try using this formula in sheet2: (add it to Sheet2, A2, then copy it around.)

=offset(sheet1!$A$1,row(a2)-1,column(a2)-1,1,1)
Ditto
  • 3,256
  • 1
  • 14
  • 28
0

To directly answer your question - you can achieve this with the INDIRECT function. INDIRECT allows you to dynamically reference a cell through a formula, which doesn't necessarily follow Excel's "tracking" rules. Keep in mind that normally, Excel gives each cell a 'unique id', and when you initially reference any cell, the internal logic points to that specific 'unique id', and the visible reference points to the 'A1' style reference to that cell. This is done so you can insert rows and columns without unintentionally losing all of your references.

It is generally not a good idea to do what I'm about to show, because you lose the inherent benefit that direct references provide (in general: easier to maintain). However, to show you how it would work, see below [this assumes you want one header row, and that the column on your results sheet should match the column on your raw data sheet]:

=INDIRECT("Sheet1!R"&ROW()+1&"C"&COLUMN())
Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46
0

Try to avoid formula volatilty, which means a formula recalculates on a change to the sheet even if its precedents have not changed.

Having numerous volatile formulas in a worksheet can cause performance issues.

Any formulas that utilize the OFFSET() function or the INDIRECT() function automatically become volatile. But of these two functions, INDIRECT is much worse than OFFSET. Both are volatile, but OFFSET is extremely fast, while INDIRECT is extremely slow.

DO NOT USE INDIRECT().

The best alternative is without question the INDEX() function. It is even faster than the OFFSET function and INDEX is not volatile.

So use the following formula in cell A2 of the 2nd sheet:

=INDEX(Sheet1!$1:$1048576,ROW(),COLUMN())

...and then copy as needed.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40