ThereIsNoSpoon
New Member
- Joined
- May 23, 2014
- Messages
- 3
- Office Version
- 2010
- Platform
- Windows
Hello all,
I would greatly appreciate help from somebody regarding this problem I currently have. It is frazzling me.
on the commercial invoice sheet that I had created, I have a slot for a commodity code to be automatically filled using a VLOOKUP formula. This is powered by a drop-down menu that is populated by all of our asset numbers.
as it stands, the form works well.
BUT...
It only looks up the Export code on our asset database.
To get around this I had made two sheets for import and export. I had hoped to be able to combine them so that I only have the one form to maintain.
So I would need a formula that looks at the asset number and the import/ export drop-down boxes for the search criteria. This would still look at the same asset database but search both the import and export columns (which are next to each other on the same table) to return the value that matched.
I'm not even sure that this can be done but any advice on this would really put me out of my misery.
The formula that I am using to return a value is:
=IFERROR(IF(VLOOKUP($K14,tbl_TJL_Data,9,FALSE)=0,"",VLOOKUP($K14,tbl_TJL_Data,9,FALSE)),"")
The IFERROR and IF functions are there to return a blank if the sheet has no data on it or if the asset has a blank in its entry, zeroes, etc. ( i am sure that it looks clunky, there may be a better way to present that too...)
K14 is the drop-down for the asset.
tbl_TJL_Data is the asset table name.
Column 9 is for the Export code. so column 10 is for Import codes.
My Import/ Export drop-down lives on cell N13.
I hope that I have explained all this clearly enough because reading it over seems like a mess.
Many thanks
Rob
I would greatly appreciate help from somebody regarding this problem I currently have. It is frazzling me.
on the commercial invoice sheet that I had created, I have a slot for a commodity code to be automatically filled using a VLOOKUP formula. This is powered by a drop-down menu that is populated by all of our asset numbers.
as it stands, the form works well.
BUT...
It only looks up the Export code on our asset database.
To get around this I had made two sheets for import and export. I had hoped to be able to combine them so that I only have the one form to maintain.
So I would need a formula that looks at the asset number and the import/ export drop-down boxes for the search criteria. This would still look at the same asset database but search both the import and export columns (which are next to each other on the same table) to return the value that matched.
I'm not even sure that this can be done but any advice on this would really put me out of my misery.
The formula that I am using to return a value is:
=IFERROR(IF(VLOOKUP($K14,tbl_TJL_Data,9,FALSE)=0,"",VLOOKUP($K14,tbl_TJL_Data,9,FALSE)),"")
The IFERROR and IF functions are there to return a blank if the sheet has no data on it or if the asset has a blank in its entry, zeroes, etc. ( i am sure that it looks clunky, there may be a better way to present that too...)
K14 is the drop-down for the asset.
tbl_TJL_Data is the asset table name.
Column 9 is for the Export code. so column 10 is for Import codes.
My Import/ Export drop-down lives on cell N13.
I hope that I have explained all this clearly enough because reading it over seems like a mess.
Many thanks
Rob
