I have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)
for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. any idea how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked
What do you mean by "which is depenant on the values selected in the previous three lists"?
can you post an excel sheet, showing where you're at and what you want?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I'll make a basic example tonight with what I mean if needed
Like from dropdown list A I choose Client, From dropdown list B I choose Location and dropdown list. In the third dropdown list I need products but the products need to be specific to the cleint and location... thats a simplified example as I am unable to upload the file.
All the information is in one large table. I made the first two lists using the above formula as that works when it is depdent on one list but not sure how to change it so the formula checks both lists selecting rows where only D15 and F15 match the corresponding values.
It will all depend on where you have your list of possibilities and how they are structured.
Despite the high cost of living, it remains very popular.
Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!
Is there not a basic way to say MATCH Cell B15 with list Table1 and Cell D15 with Table 2 and Cell F15 with Table 3?
Possibly, yes. But it is easier to find a solution when we can see what you are talking about!! From the sound of it, assembling a list, dependent on your inputs might be a runner, but I don't want to second guess what you've already got.
Example.xlsx
That is a simplified example of how I have set it out but its on a massive scale and needs to reference all previous columns for the productl ine
Not positive this is what you are looking for but put this into E2 and press ctrl + shift + enter
Formula:
=IFERROR(INDEX(Product,MATCH(B2&C2&D2,Client&Country&Location,0)),"")
If there are more than 1 product per option of the previous 3, and a lot more options of those it is good to show some variety in the sample, along with what you expect the result to be, so we know more of what you are looking for.
Hi
No need array formula from Hawkeye16
Use this E2enter.Formula:=IFERROR(INDEX(Product, MATCH(B2&C2&D2, INDEX(Client&Country&Location, 0), 0)),"")
Regard
micope21
To help you by my post? it would be nice to click onto say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
Meh. I'd prefer the array formula that is shorter over a non array formula that is longer. Personal preference I suppose. I prefer simpler.
Does that formula work in the data validation? Cant seem to get it to work, the cell which the formula is being entered also isnt in line its on another sheet would that cause any issue as its reference the correct cells and lists.
There is no need for data validation unless there is more than one value possible, you might as well just use a formula in your example and take the user work out of the equation for that.
If the example is not showing a lot of the possible combinations or different situations that arise, then we may need to rework our solution when we get a more appropriate example file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks