Keep " IF " formula working after inserting cell

dannac

Active Member
Joined
Nov 17, 2004
Messages
472
Worksheet to keep track of mail route. Sample sheet below will have approx 900 boxes.

Cols B, E, H, etc will input actual box number.
Cols A, D, G, etc keep numerical order of boxes.

Row 2 at cells C2, F2, I2, etc will show number of boxes on that street.


Some of the formulas used

A4 =IF(B4="","",A3+1)
D4 =IF(E4="","",D3+1)
G4 =IF(H4="","",G3+1)

D3 = =MAX(A3:A37)+1
G3 = =MAX(D3:D37)+1

Route9_zpsfff4cc51.jpg


Problem comes when I need to insert a new box number between two others.
Say on Waverly Rd ... I need to put box 140 between 138 and 144.

Inserting cells throws all the "if" formulas off, and I would have to do some
copying down the entire column again.

Appreciate some ideas or fixes.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Can't you just use absolute cell referencing in your formula? A4 =IF(B$4="","",A$3+1) or am I missing something?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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