BrandynBlaze
New Member
- Joined
- Sep 20, 2012
- Messages
- 29
Hello,
I'm having a difficult time figuring out the best way to approach a problem and I was hoping someone could help.
I'm trying to return a value based on two drop down lists. I've created 3 tables that contain the data that I want returned depending on the first drop down by creating the named references "Formulations", "Raw Materials", and "Distillations".
I then created a 4th table that is just a list of those tables. In order to create my drop-down menu I used data validation to list the 3 values in that 4th column. Using the "=Indirect()" formula in the data validation I was able to get my second list to populate with the values found in each of the 3 tables I wanted to reference depending on what the first drop down value is. What I have been unable to do so far is return the value in the cell directly to the right of the cell the second drop down references, which would be the corresponding density. I have a feeling that I need to use Vlookup but I'm not sure how I could reference the different tables based on the drop down selections. It seems that if I could just reference the cell location that was populating my second drop down list and add 1 to the column it would be the easiest way, but I'm not sure how I would do that without VBA. I'm avoiding that solution because the spreadsheet will be used by people with very little Excel experience and I want it to function as close to a normal spreadsheet as possible, with no macro or privacy warnings.
Hopefully that is enough info, I tried to post some pictures to help explain the process but I'm having trouble finding a way to host them so I can use a URL.
Brandyn
I'm having a difficult time figuring out the best way to approach a problem and I was hoping someone could help.
I'm trying to return a value based on two drop down lists. I've created 3 tables that contain the data that I want returned depending on the first drop down by creating the named references "Formulations", "Raw Materials", and "Distillations".
I then created a 4th table that is just a list of those tables. In order to create my drop-down menu I used data validation to list the 3 values in that 4th column. Using the "=Indirect()" formula in the data validation I was able to get my second list to populate with the values found in each of the 3 tables I wanted to reference depending on what the first drop down value is. What I have been unable to do so far is return the value in the cell directly to the right of the cell the second drop down references, which would be the corresponding density. I have a feeling that I need to use Vlookup but I'm not sure how I could reference the different tables based on the drop down selections. It seems that if I could just reference the cell location that was populating my second drop down list and add 1 to the column it would be the easiest way, but I'm not sure how I would do that without VBA. I'm avoiding that solution because the spreadsheet will be used by people with very little Excel experience and I want it to function as close to a normal spreadsheet as possible, with no macro or privacy warnings.
Hopefully that is enough info, I tried to post some pictures to help explain the process but I'm having trouble finding a way to host them so I can use a URL.
Brandyn