SergSlim
New Member
- Joined
- Feb 18, 2013
- Messages
- 19
Hello.
I've uploaded an example file:
https://docs.google.com/file/d/0B9PWiv0aD6B2VUVlZENNVmRIWU0/edit?usp=sharing
I have a formula with multiple IFs:
=IF($B6="","",IF($B6=Дані!$D$24,"",IF(ISNA(VLOOKUP(C6,Установка!H:H,1,FALSE)),"Не встановлено","Встановлено")))
Well, what is does:
1. If B6 is empty, then D6 (where formula is) would be empty
2. If B6 is equal to $D$24 from other sheet, then D6 would be also empty
3. Now I compare serial number of equipment (C6) with the list of serial numbers on Install (Установка) sheet in column H. If there is a match, than it is "Installed" ("Встановлено"), if there is no match, then is it "Not Installed" ("Не встановлено").
Everything works fine until I get the same serial number listed twice. Same equipment was installed, returned, and came back to my warehouse.
Now it incorrectly says that it is installed, because it was installed earlier before I got it for the second time.
To fix this, I have to compare a date in column A with date on sheet Install (Установка) in column B. So, in this formula I have to add another IF condition in the last step instead of ,"Installed".
But this is not that simple. I have to compare a date from cell A6 with date from range on Install sheet, but only for those rows that match previous IF clauses, otherwise it will always match date.
So to summarize, when formula gets to "Installed" step, it has to do another check. There might be several cells on Install sheet that will cause it to get to "Installed" step. I have to check whether date in A6 is not less than date in appropriate cells that caused formula to get to "Installed" step. If the date is less, then print "Not Installed". If the date is equal or greater than the date on Install sheet, then print "Installed"
In the example file I attached both D3 and D6 say that it was installed, even though only 1 cell on Install (Установка) sheet has the same serial number. We can see that D6 was recieved on 1/2/2013, which is later than it was actually installed (9/1/2012). So for D3 it should say Installed, while for D6 - Not Installed. D6 should change to installed after the same serial will be on Install (Установка) sheet and date in B2 on that sheet would be >= date in A6 of
Реєстр приходу sheet.
Is it possible?
I've uploaded an example file:
https://docs.google.com/file/d/0B9PWiv0aD6B2VUVlZENNVmRIWU0/edit?usp=sharing
I have a formula with multiple IFs:
=IF($B6="","",IF($B6=Дані!$D$24,"",IF(ISNA(VLOOKUP(C6,Установка!H:H,1,FALSE)),"Не встановлено","Встановлено")))
Well, what is does:
1. If B6 is empty, then D6 (where formula is) would be empty
2. If B6 is equal to $D$24 from other sheet, then D6 would be also empty
3. Now I compare serial number of equipment (C6) with the list of serial numbers on Install (Установка) sheet in column H. If there is a match, than it is "Installed" ("Встановлено"), if there is no match, then is it "Not Installed" ("Не встановлено").
Everything works fine until I get the same serial number listed twice. Same equipment was installed, returned, and came back to my warehouse.
Now it incorrectly says that it is installed, because it was installed earlier before I got it for the second time.
To fix this, I have to compare a date in column A with date on sheet Install (Установка) in column B. So, in this formula I have to add another IF condition in the last step instead of ,"Installed".
But this is not that simple. I have to compare a date from cell A6 with date from range on Install sheet, but only for those rows that match previous IF clauses, otherwise it will always match date.
So to summarize, when formula gets to "Installed" step, it has to do another check. There might be several cells on Install sheet that will cause it to get to "Installed" step. I have to check whether date in A6 is not less than date in appropriate cells that caused formula to get to "Installed" step. If the date is less, then print "Not Installed". If the date is equal or greater than the date on Install sheet, then print "Installed"
In the example file I attached both D3 and D6 say that it was installed, even though only 1 cell on Install (Установка) sheet has the same serial number. We can see that D6 was recieved on 1/2/2013, which is later than it was actually installed (9/1/2012). So for D3 it should say Installed, while for D6 - Not Installed. D6 should change to installed after the same serial will be on Install (Установка) sheet and date in B2 on that sheet would be >= date in A6 of
Реєстр приходу sheet.
Is it possible?
