Hello
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves.
If you can have a look at a test file I attached you will see the full picture.
I have 2 tables, where the 2nd one is on the right side of the 1st one.
1st table:
A B C D E ... AF
1 PRODUCTS
2CDIR No Vehicle No 1 2 3 30
3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
4CDIR00001 012010001 WBT959 WBT960
5CDIR00001 1000 2000
6CDIR00002 0120110002 WBT1239 WBT524 WBT623
7CDIR00002 500 210 750
Where WBT959 is a product and 1000 - a qty.
2nd table:
AH AI AJ AK AL AM ...
1xxxxxxxxxxxxxxxxxxxx| 1 | 2 |... | 30 |
2CDIR No Veh. No Part No Qty Part No Qty Part No Qty
3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
4CDIR00001 012010001 WBT959 1000 WBT960 2000
5CDIR00002 ...
For one report there is ony 1 vehicle number possible.
In 2nd table I used a formula: =if(vlookup($AH4,$A$4:$AF2000,3,false)="","",vlookup(($AH4,$A$4:$AF2000,3,false)) to get my part in cell AJ4.
It worked fine.
To get the qty in cell AK4 I used a formula: =if(vlookup($AH4,$A$4:$AF2000,3,true)="","",vlookup(($AH4,$A$4:$AF2000,3,true)). This one didin't work but when I changed the data range to A4:C7 it did show me the qty I wanted.
If anyone has any idea how to solve it please help me as I am stucked on it for last week or longer and really is a head cracker.
Thank you for reading it andgiving it a thought.
Many thanks
Simon
Bookmarks