INDIRECT Function

[Sundae]

Hi All,

I used to think I was an excel expert, but I am being tested today.

I have an Excel spreadhseet where

A1 = 1
A2 = A1 + 1 = 2
A3 = A2 + 1 = 3
A4 = A3 +1 = 4
A5 = A4 + 1 = 5
ETC...

If a user of the Excel spreadsheet deletes row 3, then rows 4 - ETC turn to #REF!. How can I avoid this and keep the formual flowing?

Do I want to use INDIRECT?

Many thanks,

 

[circledchicken]

Hi,

Perhaps in A1 use =ROW() instead, and drag down.

 

[LxQ]

I'm not sure if there is a better way, but this might work in A2:
=INDIRECT(ADDRESS(ROW(A2)-1,1,,,))+1

then copy the formula down

 

[Aladin Akyurek]

Hi All,

I used to think I was an excel expert, but I am being tested today.

I have an Excel spreadhseet where

A1 = 1
A2 = A1 + 1 = 2
A3 = A2 + 1 = 3
A4 = A3 +1 = 4
A5 = A4 + 1 = 5
ETC...

If a user of the Excel spreadsheet deletes row 3, then rows 4 - ETC turn to #REF!. How can I avoid this and keep the formual flowing?

Do I want to use INDIRECT?

Many thanks,

In A1 enter and copy down:

=ROWS(INDEX(A:A,1):A1)

 

[markmzz]

Try this:

Note: a small modification on the LxQ's formula.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=INDIRECT(<font color="Blue">ADDRESS(<font color="Red">ROW(<font color="Green">A2</font>)-1,COLUMN(<font color="Green">A2</font>)</font>)</font>)+1</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Sundae]

Thanks for the feedback.

Let's take it a step further.

Suppose, #1 actually appears on Row 22 and then #2 is five rows down on Row 27?

 

[markmzz]

Thanks for the feedback.

Let's take it a step further.

Suppose, #1 actually appears on Row 22 and then #2 is five rows down on Row 27?

And #3 row?

Markmzz

 

[Sundae]

row 32, each increase by 1 will be five rows down.

 

[LxQ]

If your numbers are always consecutive, meaning 1 is in row 22, 2 is in 27, and 3 would be somewhere after 27, then in A27 you can just use:

=MAX(A$1:A26)+1

The problem with this though (and probably the solutions before, is let's say you have numbers 1 through 7, and someone deletes the row where 4 was. Then 5 becomes 4, and 6 becomes 5, and what used to be 7 becomes 6

Edit: for five rows down you'd use ROW(A27)-5 in the formula

 

[Sundae]

Perfect - thank you!!