Ok so basically I have a 4-5 categories to choose from in my sheet, and each drop-down is contingent upon the next one.
I'm using =IF(F12="",INDIRECT(D12&"List"),E12) as well as =IF(G12="",INDIRECT(D12&E12&"List"),F12) and so on and so forth or some formula similar to that. The problem is basically that when I fill in D12 and E12 I get another dropdown in F12 that is correct, but i also get the exact same dropdown in G12, which is completely incorrect and should not be possible. How can I change it so that the dropdown I recieve in G12 is dependent on all those before it, but is not applicable before i've seleceted F12?
Thanks in advance guys!
Hi CleanPower and welcome to the forum.
The topic I think you are wanting to know is "Cascading Dropdown Lists"
Watch this video for your answer. http://www.bluepecan.co.uk/excel_tra...alidation.html
If this isn't it, respond to this answer and it will pop to the top
One test is worth a thousand opinions.
Click the * below to say thanks.
With all due respect, I don't believe you read my post in its entirety. My cascading drop downs are working, and I don't require a tutorial for how to use =indirect (which creates a whole other problem in this case as it allows mismatched columns which I cannot allow as customers will be using this sheet)
To reiterate, lets say for example we have 5 cascading dropdowns:
A1 B1 C1 D1 E1 all with seperate subsets and lists, however the formula in B1 would look like =IF(C1="",INDIRECT(A1&"List"),B1) which basically checks C1 to see if B1 is allowed to change, if theres nothing in C1, or if the conditions match, you can change B1. If there is a value that does not match, you cannot change B1, and this is what i have so far.
My problem is that when i use that formula with the proper cells substituted the same dropdown that would be in B1, is also in C1, the issue being that i have repeated values in some of my catagories, not all of them, and thus the names must be combinatory (so my formula looks like =IF(D1="",INDIRECT(A1&B1&"List"),C1) =IF(E1="",INDIRECT(A1&B1&C1&"List"),D1) etc etc)
Basically what Im asking is how can I program it so that if C1 can check if there is nothing in B1 so that it wont allow the same list in B1 to be in C1
Hey CleanPower, be nice as I'm unpaid labor. In my defense you didn't say you knew about "Cascading" dropdowns in your first post.
Without reading your full discpription, once again, it seems to me you need to build a string of words and if one is blank, not have it show. I'm wondering if the Trim function in front of the Indirect or perhaps the A1&B1&C1 might work. It is pretty hard to understand what exactly I'm looking at without an example workbook.BTW - you can attach an example workbook by clicking on the "Go Advanced" below the message area and then click on the Paper Clip Icon above the advanced message area.
Now back to your problem....
So if you have Bob & Bob & Fred, you want it to leave the repeat out and only give back BobFred. Is that the question?
More specifically in your example of:
If A1 and B1 are the same then don't use B1. How about this=IF(D1="",INDIRECT(A1&B1&"List"),C1)
=IF(D1="",INDIRECT(A1 & IF(B1 = A1,"",B1) &"List"),C1)
One test is worth a thousand opinions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks