Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Smart VLookup...
My part Number has two "faces" numeric code and Character code
Example Prod1 is xxxyz numeric say 911-y-z, the y is spec like 10cm,.. and the z is color So, Item 91113 Ribbon , 10cm ,and yellow. It also has a name: Ribbon ,10 CM and YELLOW. If I gave a value of 91100 to Ribbon and 10 to "10 CM" and a 3 to Yellow then I can transform: Ribbon 10 CM Yellow to 91100+10+3= 91113 Now the fun part: I have Column A = Item Description B = Specs c = Color I built 3 Tables to convert Character to Value So in Column D I have =Vlookup..+VLookup...+VLookup.... The problem is Colum B Table There are Several of them Depending on The Result of Table A selection So, back to the example if I have Item 922 I must go to a different B-Table because it has a DIFFERENT specs say weight Item 92233 is Powder, 10 gm , Yellow ( color applies to all items) Table B2 is different here (it is for Powder) I have 15gm / 10 23gm/ 20 35gm/ 30 While Table B1 is:( it is for Ribbons) 10cm/ 10 15cm/ 20 40cm/ 30 So back to the Formula in Colum D THe middle Table must be selected based on the value in Column A I have to program this rather than build a complicated formula.Especially these tables are dynamic. Products can expand with the consequence of different B tables. In case you may be confused by my naming I call the table for column A A-Table and Column B B-Table etc Appreciate your direction in the matter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Smart VLookup...
Appreciate your direction in the matter
Try using the INDIRECT function in your second lookup. Construct a lookup that returns the name of the B table associated with each product. Say this lookup returns the name of the B table in cell A1. So A1 will have "Table B1" or "Table B2", etc. Your spec lookup will do something like this: =VLOOKUP(Spec,INDIRECT(A1),2,False) -- Jim "Arishy" wrote in message oups.com... | My part Number has two "faces" numeric code and Character code | Example | Prod1 is xxxyz numeric say 911-y-z, the y is spec like 10cm,.. and the | z is color So, Item 91113 Ribbon , 10cm ,and yellow. | | It also has a name: Ribbon ,10 CM and YELLOW. | | If I gave a value of 91100 to Ribbon and 10 to "10 CM" and a 3 to | Yellow then I can transform: | Ribbon 10 CM Yellow to 91100+10+3= 91113 | | Now the fun part: | | I have Column A = Item Description | B = Specs | c = Color | | I built 3 Tables to convert Character to Value | So in Column D I have =Vlookup..+VLookup...+VLookup.... | | The problem is Colum B Table There are Several of them Depending on The | Result of Table A selection | | So, back to the example if I have Item 922 I must go to a different | B-Table because it has a DIFFERENT specs say weight | | Item 92233 is | | Powder, 10 gm , Yellow ( color applies to all items) | | Table B2 is different here (it is for Powder) | I have 15gm / 10 | 23gm/ 20 | 35gm/ 30 | | While Table B1 is:( it is for Ribbons) | 10cm/ 10 | 15cm/ 20 | 40cm/ 30 | | So back to the Formula in Colum D THe middle Table must be selected | based on the value in Column A | | I have to program this rather than build a complicated | formula.Especially these tables are dynamic. Products can expand with | the consequence of different B tables. | | In case you may be confused by my naming | I call the table for column A A-Table and Column B B-Table etc | | Appreciate your direction in the matter | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Smart VLookup...
I used your technique in creating column A,B and C I have "Validation" lists to construct the Item description (the 3 parts). So I do have a "NAME"List with the product in A I beg you to imagine Col. A , B, C with Validation to actually help the user pick his item. To do that I used the INDIRECT for B to select the right spec based on A. Exactly as you descripted it but with validation. My problem is to repeat the SAME TECHNIQUE as you proposed but with different "Tables" in this case. So, Col. A has Ribbon ( I have a NAME with the name Ribbon to direct me to the spec for Ribbon. NOW How can I used the SAME NAME but point me to TABLE instead of a validation list; to VLOOKUP it. In your Formula INDIRECT should use THE SAME name in A1 as a reference to a table. BUT this A1 points to range name with the same name in order to create it in the first place using data validation. I know I am trying very hard to let you visualize the problem. The question is do you have the patience!!!! I sincerly hope so *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Smart VLookup...
Thanks Tushar for your response.
If I go to Names I will find "Ribbon" is sitting there very happy doing what I told it to do. Mainly waiting for the "Indirect" in the column B validation to do the redirection. Let us say "Ribbon" NAME represent Range (E1:E5) The specs for Ribbon. Now you are asking me to Name another Range this time a Table say Range (F1:G5) with the SAME name "Ribbon" From XL point of view both are ranges one column the other a table. Range(E1:E5) will contain {20cm,30cm,40cm etc) Range (F1:G5) will contain {20cm,10,30cm,20,40cm,40..etc) Will XL be confused ? I will go to the drawing board and "I will be back".... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reinstate smart tag | Excel Discussion (Misc queries) | |||
make VLINKUP like vlookup; smart hyperlink by data value | Excel Worksheet Functions | |||
Invest smart | Excel Worksheet Functions | |||
Invest smart | Excel Discussion (Misc queries) | |||
Invest smart | Charts and Charting in Excel |