Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Creating Serial Additions of Numbers Formatted "M-N" In Merged and Unmerged Cells


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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,696
Default Creating Serial Additions of Numbers Formatted "M-N" In Merged and

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?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Creating Serial Additions of Numbers Formatted "M-N" In Merged and

?B?U2VhbiBUaW1tb25z?=
wrote in microsoft.public.excel.worksheet.functions:

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.


"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?





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
zero "0" dropping from the beginning of my serial numbers Elton Jones Excel Worksheet Functions 3 December 18th 07 10:36 PM
Can cells be formatted so that if A2 says "Joan", G2 says "$25" Renee Thomas[_2_] Excel Worksheet Functions 1 September 12th 07 08:27 PM
sort spreadsheet, "merged cells" comes up. Find cells? Unmerge ? lowell Excel Discussion (Misc queries) 1 August 20th 06 09:10 AM
In Excel a cell formatted "currency" shows "######" help! llveda Excel Worksheet Functions 2 April 7th 06 09:39 PM
cells formatted to tick when text value "Y" if or null if "N" Jay Excel Discussion (Misc queries) 7 January 13th 06 10:16 AM


All times are GMT +1. The time now is 09:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"