Automatically Readjust cell references when cell above is deleted

Question

I have setup Cells A6 and down as a unique identifier with cell A7 equal to A6 + 1 and on down the line.  This number is used for v-lookup formulas and other formulas.  When I delete a row, I get a #REF in all cells below.  Naturally this causes a bunch of problems down the line.

Is there some way to make excel automatically readjust the count above in the ID column?  So, if cell A5 is deleted, A6 automatically readjusts to A4+1?  Or, is it possible to eliminate the column and row number altogether and give each cell a pure relative reference?  So, tell the cell (+1 to the cell above you); absolutely no reference to what that cell ref. actually is?  This way, when I delete a row, Excel doesn't care?

VBA is not an option for me right now; sorry.  (I want to learn it).

Answer 1

Put the following formula and drag down -

=INDIRECT("A"&ROW()-1)+1

Answer 2

Thank you so very, very much!  Please, explain to me how this works:

• Indirect: "returns the reference specified by a text string".  That sort of makes sense.  So, it's converting the string "A" and converting it into a reference (i.e. A).
• &: concatenates, or combines together, two strings; in this case, "A" and Row()-1)
• ROW():  "returns the row number of a reference".  The () indicates that we're actually leaving this blank.
• -1 tells the computer to look up by one space and the +1 tells the computer to look down one space.  So ( ) = 0 = the cell the formula is in; -1 tells the computer to look up one cell and +1 telsl the computer to look back down one cell.

Am I right?

Thank you.

Answer 3

Your first cell needs to have a value and succeeding cells need to have this formula.

Suppose, you deleted row 4. Now row 5 becomes your new row 4. ROW() will become 4 now, ROW()-1 will be 3. Hence, you formula would translate to INDIRECT("A3")+1. Hence, it will add the value in A3 with 1 and put this value in row 4.