As you mention that you can not upload file please follow by this ...
Do not use others method to upload files.
You doubt where to place formulas , your invoice number layout, I'll try my best to explain (due to my low level of English T_T ).
1. In Sheet0 , Formula is only in B_Column, C_Column and D_Column
A_Column contains only INVOICE NUMBER that without hyphen (-)
In a sample file I use formula for randomize invoice number , sorry to make you confuse.
Formula in B,C,D_Colume have been change to support hypen in price sheets.
All of them need Ctrl-Shift-Enter for Array Formula.
And again please remember to change 'all column' reference to definite range of your data rows for better performance.
(ie $B:$B => $B$1:$B$2000, $A:$A => $A$1:$A$2000)
2. In price sheets ( Sheet1, Sheet2, Sheet3 )
All of them are values (but in a sample file I use formula for randomize too)
And I've add a title row in these sheet, use a 'number' as price instead of showing text as before.
3. I've change condition formatting in price sheets (Sheet1,Sheet2,Sheet3) for support that
Some invoice number in these sheets may contains hyphen (-).
Formula is (at cell A2 in Sheet1,Sheet2 and Sheet3)
Don't forget that invoice number in Sheet0 must not contain hyphen (-), while invoice number in price sheets may contains hyphen.
Regards.
Please note : hy-light in price sheets is only tell that they was listed in Sheet0.
Bookmarks