Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation, VLookup or If ???????
I have a spreadsheet which contains the following
C1 C2 C3 C4 C5 C6 C7 CountryCode, CategoryCode, Product Code, Product Description, $A, $US, $NZ I am using Data Validation to Select the product code based on the category code selected, and using VLookup to select the corresponding product description. My question is how can I get a price selected based on the CountryCode as well. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation, VLookup or If ???????
Not sure that I really understand how your worksheet is set up but am I
correct in assuming that where you are looking up the prices you have 3 columns for the prices, $A, $US and $NZ. If so, you could insert a column (Hide it if necessary) and use vlookup to insert a number beside the country code. You can then reference that cell for the Col_index_number in vlookup. If my assumption is not correct then maybe you can post some more sampling of your lookup table/s. -- Regards, OssieMac "dragons_lair" wrote: I have a spreadsheet which contains the following C1 C2 C3 C4 C5 C6 C7 CountryCode, CategoryCode, Product Code, Product Description, $A, $US, $NZ I am using Data Validation to Select the product code based on the category code selected, and using VLookup to select the corresponding product description. My question is how can I get a price selected based on the CountryCode as well. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation, VLookup or If ???????
Sorry for the confusion. But I have 2 worksheets set up 1 worksheet contains
my lists and the prices are in 3 separate columns. The other worksheet contains the data validation structures. My objective is to produce a quotation document based on products the customer has asked to be quoted on and each country has its own price band. When using the worksheet I select the country, category and product code from drop down lists. The product description is selected using vlookup and I have also used the vlookup function to select currently only 1 price band. I have looked at a few examples I found on the web and have tried unsuccessfully to use INDEX and MATCH to try and select the correct price band but due to my inexperience in creating formulas I am having a bit of trouble. Hopefully this makes things a bit clearer "OssieMac" wrote: Not sure that I really understand how your worksheet is set up but am I correct in assuming that where you are looking up the prices you have 3 columns for the prices, $A, $US and $NZ. If so, you could insert a column (Hide it if necessary) and use vlookup to insert a number beside the country code. You can then reference that cell for the Col_index_number in vlookup. If my assumption is not correct then maybe you can post some more sampling of your lookup table/s. -- Regards, OssieMac "dragons_lair" wrote: I have a spreadsheet which contains the following C1 C2 C3 C4 C5 C6 C7 CountryCode, CategoryCode, Product Code, Product Description, $A, $US, $NZ I am using Data Validation to Select the product code based on the category code selected, and using VLookup to select the corresponding product description. My question is how can I get a price selected based on the CountryCode as well. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation, VLookup or If ???????
Hi again,
Your quote: "I have also used the vlookup function to select currently only 1 price band" Based on the above, the method I gave you should work. Instead of using the column number in the vlookup to tell the formula what value to return, use a cell value with the column number in it. You just need a table with the country code and the appropriate column number. Use vlookup to insert the number of the column in a cell based on your country code. Then use that cell reference in lieu of the column number in the price vlookup formula. -- Regards, OssieMac "dragons_lair" wrote: Sorry for the confusion. But I have 2 worksheets set up 1 worksheet contains my lists and the prices are in 3 separate columns. The other worksheet contains the data validation structures. My objective is to produce a quotation document based on products the customer has asked to be quoted on and each country has its own price band. When using the worksheet I select the country, category and product code from drop down lists. The product description is selected using vlookup and I have also used the vlookup function to select currently only 1 price band. I have looked at a few examples I found on the web and have tried unsuccessfully to use INDEX and MATCH to try and select the correct price band but due to my inexperience in creating formulas I am having a bit of trouble. Hopefully this makes things a bit clearer "OssieMac" wrote: Not sure that I really understand how your worksheet is set up but am I correct in assuming that where you are looking up the prices you have 3 columns for the prices, $A, $US and $NZ. If so, you could insert a column (Hide it if necessary) and use vlookup to insert a number beside the country code. You can then reference that cell for the Col_index_number in vlookup. If my assumption is not correct then maybe you can post some more sampling of your lookup table/s. -- Regards, OssieMac "dragons_lair" wrote: I have a spreadsheet which contains the following C1 C2 C3 C4 C5 C6 C7 CountryCode, CategoryCode, Product Code, Product Description, $A, $US, $NZ I am using Data Validation to Select the product code based on the category code selected, and using VLookup to select the corresponding product description. My question is how can I get a price selected based on the CountryCode as well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation-VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup in data validation??? | Excel Worksheet Functions | |||
Data Validation vs VLOOKUP - Linking to data in a seperate file | Excel Worksheet Functions | |||
Data Validation on Vlookup | Excel Discussion (Misc queries) | |||
data validation using vlookup | Excel Worksheet Functions |