Deleting a column and keeping correct formula.

Palacemad

New Member
Joined
May 19, 2019
Messages
42
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!
 
Last edited by a moderator:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Last edited:
Upvote 0
You're welcome and thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top