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" wrote:


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?