Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Found a SERIOUS bug in Excel !!!
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. -- irresistible007 ------------------------------------------------------------------------ irresistible007's Profile: http://www.hightechtalks.com/m63 View this thread: http://www.hightechtalks.com/t2295667 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Found a SERIOUS bug in Excel !!!
If your formulas are as unintelligible as your explanation of the problem,
it's hardly surprising if your sheet doesn't work as you expect. Fuzzy writing = fuzzy thinking. "irresistible007" wrote in message ... 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. -- irresistible007 ------------------------------------------------------------------------ irresistible007's Profile: http://www.hightechtalks.com/m63 View this thread: http://www.hightechtalks.com/t2295667 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Found a SERIOUS bug in Excel !!!
That is not a bug, but a misunderstanding by you of how merged cells works.
When you merge cells A1 and A2, you are not copying the value in A1 to A2, but just increasing the space that A1 takes up by cell A2. A2 actually still exists, and is empty, so will fail the tests in your formula. To see this, somewhere on the worksheet, enter =A2, you will see you get 0, not ABC Company. If you must persist with merged cells, you could use =SUMPRODUCT(--(MOD(ROW(A1:A116),2)=1),--(LEFT(A1:A116,3)="ABC"),--(LEFT(B1:B 116,5)="Gamma"),C1:C116)+SUMPRODUCT(--(MOD(ROW(A2:A118),2)=0),--(LEFT(A1:A11 7,3)="ABC"),--(LEFT(B2:B118,5)="Gamma"),C2:C118) -- HTH RP (remove nothere from the email address if mailing direct) "irresistible007" wrote in message ... 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. -- irresistible007 ------------------------------------------------------------------------ irresistible007's Profile: http://www.hightechtalks.com/m63 View this thread: http://www.hightechtalks.com/t2295667 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
in ms excel 2003 research tool thomson gale is not found why? | Excel Discussion (Misc queries) | |||
Value Errors with EXCEL XP not showing up in EXCEL 2000 | Links and Linking in Excel | |||
File Not Found msg when run Excel | Excel Discussion (Misc queries) |