Deleting a column and keeping correct formula.

[Palacemad]

Hi.

I am sure there is a very simple solution to this. I have a list of names across rows, underneath is a number (starting at 1). What I want to do is to have a formula that means even if a column is deleted, the number adjusts itself so that it all runs correctly.

The formula that I started off with was (starting in cell D2):

=SUM(C2+1)

However, when a cell is deleted the cells following this are effected. I tried fixing the cell but did this work. I also had a go at using the indirect formula with:

=SUM(INDIRECT("C2")+1)

However, this does not drag correctly across the rows as the reference is still made back to C2 rather than moving on one at a time. Even if adjust the formula, when a cell is deleted I receive a message telling me I have a circular reference

Thank you in advance for any suggestions!

 

[jmacleary]

Hi there. If the names start in the first column,just use the formula =COLUMN() in row 2. If the names start in a further column(e.g. it looks like your first name is in column C) then adjust the formula - for names starting in column C then D2 would become: =COLUMN()-2 - then drag across.

 

[Palacemad]

Thank you! So very simple, I wasn't thinking in that way at all!

 

[jmacleary]

You're welcome and thanks for the feedback.