My company does packaging services, and as such our orders are put together by our CSR's and sent out to the warehouse. We have a check in place so that nothing falls through the cracks, but it requires someone to physically print a report and then locate everything in the dispatch office.
Essentially, I need to compare a column of queried data to a column of input data in a different tab, and then using a rule, highlight all cells in the rows on the queried tab where the input data column contains the queried cell.
The method I am working on now is an excel sheet that imports this information onto sheet 'Data', and then the CSR's will input their order numbers on sheet 'Input'.
On sheet 'Input' I have three columns: columns A for raw input, columns B which contains the formula =NOT(ISNA(VLOOKUP($C2,$A$2:$A$300,1,FALSE))), and columns C, which contains the formula ='Data'!$B2 (B is the column with the data I need).
Then, I compare C to A using columns B, and then my rule on sheet 'Data' is ='Input'!$B2=TRUE
The issue is that 'A' and 'C' aren't recognized as equal when they should be. However, when I go to sheet 'Data' and double click the cell so that I can edit it, and then press enter, 'A' does recognize 'C', and 'B' becomes TRUE, and the rule applies formatting to the related rows. What is happening here, and how can I get 'A' to recognize 'C' automatically?
From my experience this may be the workaround: If double-clicking on the cell "triggers" excel to reformat the value in the cell, then you can quickly force a column to reformat itself by using the "Text to Columns" function under the Data tab. Try highlighting the column on the 'data' sheet that is reformatting after you double-click. Once highlighted go to Data->Text to Columns->Make sure delimited is selected->then press "finish".