Check out some similar questions!

dannac · Apr 12, 2013, 02:46 AM

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.

ScottGem · Apr 12, 2013, 03:16 AM

Since you can't insert a row, you have to copy and paste. So you copy and paste from 144 down one row just in Col E. The formulas in Col D should be unaffected.

dannac · Apr 12, 2013, 06:31 AM

Thanks ScottGem... was hoping for something better than copy and paste.

Also got a lesson today on "cross posting", which I thought meant asking the same question in different sections of the same forum. But it's posting same question two different forums.

I'm suppose to provide links.
Keep "IF" formula references after inserting cell(s)

Keep " IF " formula working after inserting cell

ScottGem · Apr 12, 2013, 09:32 AM

Many people do monitor multiple forums. I've seen questions asked on different sites and never seen anyone complain about them. But I guess ExcelForum doesn't like it,

But it seems that no one else knows of a better solution then copy and paste.