Tired and frustrated with this, so I'm asking for help as I can't figure out what's wrong. The file is attached.
Here is the background info and the issue:
In column C, I am entering a box label. So, something like D-5329. Sometimes that's the complete label. At other times, it's only a part of the label and I want appended to it a dash (-) and the number from the cell in Column A. So, D-5329-43. I want to check and see if there are duplicates, which I am doing in Column E, when I have new ones created. So, to distinguish between when the number in column A is to be appended or not, in Column D, when it's not to be appended, I am adding the following text after the words "Box Label:": "Itm#-N".
Therefore, when there are duplicates, the words "Duplicate Label" is supposed to appear instead of the box label. However, something major is off. I keep getting inconsistent results and often times when I copy and paste the formula, I get the results of the last formula shown even though when I use Excel's Evaluate Formula, the correct result is shown as the 2nd to last step.
Depending on the paste order or the order of the insertion of the text "Itm#-N" to Box Label, you will see Excel saying there is a circular reference when you go to Formulas > Formula Auditing > Error Checking > Circular References. It also tells me of this when I open the file for the first time.
Also, in case you are wondering why I am using such things asandPlease Login or Register to view this content.
, instead of the cell number (i.e. C5), and alsoPlease Login or Register to view this content.
it's because I want to be able to cut and insert cell rows without things breaking.Please Login or Register to view this content.
Right now, as the file is, cells E6 is showing the results of cell E3 instead of the value of C6, which is D-9599.
So, what's going on and what is the correct way to do the comparisons?
Thanks.
Bookmarks