04-15-2019 11:43 PM
04-15-2019 11:43 PM
I recently posted a question and was given a great response but unfortunately the sample data missed one example that I am having trouble to apply to the formula.
In the ID column (A1) there I used the example of 456A, in the formula the ISNUMERIC funtion is used to strip out the A to then do a check against the numbers. In the data I have been sent there can be something like 1ASN3Z in the G column and in my Ref table column (A) the letters SN. In this case I would be looking for the SN in 1ASN3Z, the letter code and QTY will only every appear as unique so it will be still a 1 to 1 match, I need to still match the code plus the Qty values. I am thinking it is just a case of removing the ISNUMERIC function but I am sure it wont be as easy as that. Again my apologies that I wasn't sent all the possible examples of this. I have re-uploaded an example with the working formula so far.
As a side note, unfortunately this cannot be done in VBA as the file I will be updating will be new every week so I am just looking to copy and paste the formula in to the sheet when I need it.
04-16-2019 03:41 AM
04-16-2019 06:14 AM
Hi @Twifoo, thanks for the reply, I am not sure if you are asking or making a statement. If the value in column A is not found at all then no other checks need to be made and the Sal column can just be set to 0. What I need to happen is to look for the value that appears in A, in the G column, if there is a match and the Qty match then add in the Sal value from C in to column I. If the Qty in D is 0 then the Sal is also 0 no matter what the ID value in column G is.
04-16-2019 10:18 AM
04-16-2019 12:41 PM
My apologies @Twifoo I wasn't quite sure from your initial response. In relation to my original question I am not looking to reverse logic by changing the lookup value to the lookup column. But I do see some issues in my original logic as well as a strange error I am not sure about.
As I mentioned columns A to D are my reference values and G to K is where I am updating the data to based on when the ID and Qty match or if Qty is 0 then Sal will be 0 no matter what the ID value says.
My example already does what I want it to do but what I have noticed is, if I add a new value to the ID column in the reference section column (A) it invalidates row 9 in where I am updating my data to which I don't understand where previously it was ok but new records added to the G to K section are fine. Hopefully my example will show where the issue is.