Bug with merging the cells

irresistible007

Board Regular
Joined
Nov 24, 2005
Messages
173
Dear everyone,

I have found today a very annoying bug today while working on Excel 2003, below is the thing, please advise your opinion as how should i troubleshoot the issue:

I have a Sheet detailing each and every order of various products like in this way:

A= Customer name, B= product name, c= quantity.... (other are irrelevant to mention)

Sometimes our customer request partial shipment so lets say the product is Gamma, i enter info in excel in following way:

A1(merged with cell A2)= ABC company, B1 & B2 (not merged)= Gamma, C1=5, C2=10

At the end of this huge list i have made a simple summary for quick ref. in cell E141 I put an array formula to know how much a specific customer has bought a specific product:

E141 {=SUM(IF((LEFT(A1:A117, 3)="ABC")*(LEFT(B1:B117, 5)="Gamma"), C4:C117, 0))}

The above formula gave me after summing C1 (didnt included C2)

I have checked for the reason and found that when i click cell A1 merged with A2... It shows under name Box A1 so in this way A2 lost its identity !! hence when the above formula calculates it didnt checked for cell C2...

I can troubleshoot by simply entering the customer name separatly into A1 & A2 but I wouldn't prefer this cuz merged cells indicates same order to me and you would probably agree that its nonsense to mention the customer's name 2 times instead of mentioning 1 time for his single order with two separate shipments...

I think i have pointed towards a serious issue.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
irresistible007

Not sure that I have completely understood this, but instead of merging the 2 cells, would the 'Centre Across Selection' format be any good? This should retain the identity of the 2 underlying cells.
 
Upvote 0
Aha! Another "merged cells" frizzle-bobble.

Maybe this would help - instead of merging A1 & A2 (and the rest of the pairs in column A), try using =A1 in cell A2, and changing the color of the font to white (or whatever the background color is). It would then appear as below, but with cells A2 & A4 appearing to be blank (Colo's utility doesn't support font color changes).
Book1
ABCD
1ABC CompanyGamma12
2ABC CompanyGamma15
3FUBAR CompanyDelta4
4FUBAR CompanyUpsunose8
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top