Keep " IF " formula working after inserting cell

[dannac]

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

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.

 

[dannac]

bump

 

[ad1234]

Can't you just use absolute cell referencing in your formula? A4 =IF(B$4="","",A$3+1) or am I missing something?

 

[dannac]

Thanks for the reply but that does not work.

 

[arthurbr]

Cross post Keep "IF" formula references after inserting cell(s)

 

[dannac]

Cross post Keep "IF" formula references after inserting cell(s)

What doe cross post mean ?

 

[VoG]

What doe cross post mean ?

Please read Excelguru Help Site - A message to forum cross posters

 

[dannac]

Ok ... sorry.

I thought the cross post was for different sections of same forum.

This same question was also asked at Excel forum.
Link Keep "IF" formula references after inserting cell(s)

 

[dannac]

and

Keep " IF " formula working after inserting cell - Ask Me Help Desk