Creating Serial Additions of Numbers Formatted "M-N" In Merged and Unmerged Cells

[Patient Guy]

I have a column where I have a list of items numbered both by section
(call it integer M) and by the item within the section (call it integer
N). Thus the format of the cell value is "M-N".

In section 1 (i.e., M = 1), I have several items.

Suppose item 1 in section 1 starts in cell A4. Thus the value (no
formula) in cell A4 is:

A4 --> "1-1" formatted as General

Cell A5 is given the following formula

A5 --> =LEFT(A4,FIND("-",A4))&VALUE(RIGHT(A4,FIND("-",A4)-1))+1

The value of A5 presents at "1-2", the next item.

This is NOT a problem if all items are given row-by-row in the worksheet.

BUT in my list, an item has multiple rows (for instance, I have multiple
vendors/manufacturers of an item, and I list them within the item).

Thus I have some merged cells in column A.

When I come to cell A16, it is the merge of A16-A18, but contains
the formula above and presents as "2-2" because A15 (not merged) has value
"2-1".

The next row for the item is A19, which ALSO contains the formula with
changing cell reference, but the value is presented as "2-5" instead of an
expected "2-3"

The formula for A19 for clarity is:

A19 --> =LEFT(A18,FIND("-",A18))&VALUE(RIGHT(A18,FIND("-",A18)-1))+1

But A18's value is actually A16's value because of the merge!!

Or is it?

How do I resolve this?

 

[Sean Timmons]

I actually get a #VALUE when attempting to reference cell A18, since it
technically doesn't exist after the merge. Different issue, but an issue
nonetheless. the obvious fix would be to unmerge 16-18......

 

[Patient Guy]

I actually get a #VALUE when attempting to reference cell A18, since
it technically doesn't exist after the merge. Different issue, but an
issue nonetheless. the obvious fix would be to unmerge 16-18......

I am using Excel 2007. I got #value error at first and did not know what
to do at first. So I did a bit-by-bit formula build-up: start with
find() and then workup to more containing functions. It does come out.
If you use an "evaluate formula" it reports that something is a constant
when that is not the case (probably thinking the cell reference is not a
cell reference).

As for unmerging, that actually DOES produce a #VALUE error, as A19
attempts now to reference an empty A18. If I have to go in and fiddle
with the formula, then I might as well just manually enter in the "M-N"
format text (i.e. use no formula), since the idea of using the formula is
to do a down-fill whenever I need to re-sequence items after an
insert/delete row.