Data Validation: List deducts items already chosen

[wedzmer]

Is it possible to have a data validation that is "somewhat" dependent on what the previous data validation list has been chosen?

Example, in Cell A1, the data validation list are as follows:

• Apple
• Orange
• Lemon
• Grapes

If user chooses Orange in cell A1, the data validation list in cell B1 should only be:

• Apple
• Lemon
• Grapes

since "Apple" is already chosen in the previous cell, and so on...

I attached a sample file for more visualization of my end goal...

Thanks everyone!

[Bo_Ry]

Please try at

Fruit B1:B5
=INDEX($A$1:$A$5,AGGREGATE(15,6,ROW(A$1:A$5)/ISNA(MATCH($A$1:$A$5,OFFSET(INDIRECT(CELL("address")),,,,-CELL("col")),)),ROWS(B$1:B1)))

Customer DV list at B2:D6
=OFFSET(Fruits!$B$1,,,COUNTIFS(Fruits!$B$1:$B$5,"*"))

[wedzmer]

thanks for the response...

the problem with this is that on the 2nd customer, the chosen items on the first customer are also taken out. the data validation should only work on the said customer's 2nd and 3rd choice and so on... it shouldn't affect the other customers options.

i also tried adding up more items in the list which doesn't work with the formula anymore..

[Bo_Ry]

Double click or press F9 before selecting new customer


or add this code to sheet1

Please Login or Register  to view this content.

[wedzmer]

Unfortunately, my office pc is too slow, i forgot to mention that I couldn't bring my laptop to our office, and as such, i'm restricted with MS Excel 2007... I don't think VBA would be a route that's possible in my case.

[JeteMc]

If your office computer showed a result for Bo_Ry's formula on the Fruit sheet cells B1:B5 then it is not running the 2007 version of Excel because the AGGREGATE function is not supported in versions older than 2010.
It is important to know the correct version so that we can use the appropriate functions in our proposed formulas.
Let us know if you have any questions.

[wedzmer]

no, i tested it here on my laptop at home. but the file would be used in the office. that's why i was hoping to make it work in an older excel version.
that's why i really apologized for not clearing the office version out from the very start. i was focused on making the objective work and accomplish it.

so, to be clear, i can only hope to run it on an older excel version, preferably 2007. as to why we didn't upgrade? the office can't... i mean, i work in a 3rd world country that has a very low budget on agency development.

[Bo_Ry]

Last try

Fruit B1:B5
=INDEX($A$1:$A$5,SMALL(IF(ISERROR(MATCH($A$1:$A$5,OFFSET(INDIRECT(CELL("address")),,,,-CELL("col")),)),ROW(A$1:A$5)),ROWS(B$1:B1)))

Confirm with Ctrl+Shift+Enter

Double click or press F9 before selecting new customer


or add this code to sheet1

Please Login or Register  to view this content.

[wedzmer]

really appreciate for trying man... but it still doesn't work...
the second row will still remove the items that were already chosen in the first row. though the first row choices in the Customer sheet works.
the array function does work when we add new items in the Fruits sheet.. it's just that when any item/fruit is chosen by the first customer, all of these choices that were chosen are also removed in the DV of the 2nd customer. the removing of items must only be for that particular customers choices in 1st, 2nd, and 3rd which is columns B, C, and D. but it must not include the DVs in the next customer and so on.

[JeteMc]

Here is a method that I believe will do what you want in the 2007 version.
1. On the Fruits sheet put the customer names in B1:G1
2. Populate B2:G6 using: =SUMPRODUCT((Customer!$B$2:$D$6=$A2)*(Customer!$A$2:$A$6=B$1))
3. Populate H1:L1 with the customer names
4. Populate H2:L6 using**: =IFERROR(INDEX($A$2:$A$6,SMALL(IF(B$2:B$6=0,ROW(B$2:B$6)-ROW(B$1)),ROWS(H$2:H2))),"")
5. Produce a named range for each customer (see the linked video starting at 14:14 for quick way to do this) https://www.youtube.com/watch?v=ws63XhhQi1o
6. Use the following for the source of the data validation drop downs: =OFFSET(INDIRECT($A2),,,SUMPRODUCT(--(INDIRECT($A2)<>"")))
Let us know if you have any questions.

[wedzmer]

This worked perfectly man! Thanks a lot...

but, I just have a few clarifications...

1. Since in step 5, i'm gonna create a name range for each customer, does that mean i'm gonna make name ranges for a list of respondents in my masterfile that's about 600+ individuals which also grows through time...?
2. In the file you attached, you used an array function in the formulas, will it work even without the array function or is there another way to avoid it? Just out of curiousity.

[JeteMc]

As to #1, producing the named ranges is the least complicated method I know of, perhaps someone else will offer a better way of doing this. The method shown in the video is the best way that I have found to produce multiple named ranges although I can see that for 600+ customers even that would be tedious.
As to #2, I don't know of an alternative to using an array entered formula in H2:L6, using the 2007 version. Starting with the 2010 version the AGGREGATE function could be used to produce a regular formula.

[Glenn Kennedy]

Just one Named Range required for this:

I called it DV:

NDEX(Fruits!$H$2:$L$2,MATCH(Customer!$A2,Fruits!$H$1:$L$1,0)):INDEX(INDEX(Fruits!$H:$L,,MATCH(Customer!$A2,Fruits!$H$1:$L$1,0)),SUMPRODUCT(--(LEN(INDEX(Fruits!$H$1:$L$100,,MATCH(Customer!$A2,Fruits!$H$1:$L$1,0)))>0)))

If there are more than 100 fruits, adjust the bit in red... do not go mad, though. If you set to 10,000 and you only have 20... performance may get slow.

I also changesd the arra formula in H2 to this (copied across and down):

=IFERROR(INDEX($A:$A,SMALL(IF(B$2:B$100=0,ROW(B$2:B$100)),ROWS(H$2:H2)))&"","")

Change the bits in red to cover all customers and copy down as far as needed (remembering to adjust the Named Range if you copy down > 100 rows).

No use of the volatile INDIRECT function, either....

[wedzmer]

Just one Named Range required for this:

I called it DV:

INDEX(Fruits!$H$2:$L$2,MATCH(Customer!$A2,Fruits!$H$1:$L$1,0)):INDEX(INDEX(Fruits!$H:$L,,MATCH(Customer!$A2,Fruits!$H$1:$L$1,0)),SUMPRODUCT(--(LEN(INDEX(Fruits!$H$1:$L$100,,MATCH(Customer!$A2,Fruits!$H$1:$L$1,0)))>0)))

The attached file works perfectly but when I apply the DV Code above, I always get a prompt that says:

"ALERT: The Source currently evaluates to an error. Do you want to continue?"

I tried to paste my code in a cell and it works, but the DV just won't accept it. I believe I correctly created a name range for it and even named it as simple as my own name. Lol

Can you somehow guess what's possibly wrong with it?

[Glenn Kennedy]

Paste the formula that you used... and the name of the sheets in your real data that correspond to Fruits and Customer. That aside, it's almost impossible to tell what you've done wrong... without seeing what you've done.

[Glenn Kennedy]

I just noticed that the DV formula in the THREAD has lost the

=I

at the beginning of it... That needs to be there to get it to evaluate as a formula. It'll be OK in my file, of course.

[wedzmer]

Paste the formula that you used... and the name of the sheets in your real data that correspond to Fruits and Customer. That aside, it's almost impossible to tell what you've done wrong... without seeing what you've done.

I think I've tried to redo it a hundred times already.. lol

this is what I used in my DV call-out:

Please Login or Register  to view this content.

based on the code you used:

Please Login or Register  to view this content.

I just can't get it to work... if you'd be so much kind enough to look at the masterfile where I used the aforementioned principle, please do...

The Customers Sheet is the INPUT_SHEET for the school and the Fruits Sheet is the iSF_Checker and iSF_Checker2 respectively... I tried to separate the "fruits sheet" contents into different sheets in order to see where I could possibly correct some errors if there's anything wrong with it...

The last problem I only encounter is the DV call-out.

PS
I can't attach it here anymore, the file tend to be larger than I thought... so, I uploaded here on my google drive... ---> Masterfile

[Glenn Kennedy]

I have it running... the refs to B2 had changed to B104768... but it is impossibly slow. For ease of checking I renamed the isfchecker as sheet a. leave it that way for now. The DV is applied oNLY to the dark pink cells. Check it... but for now BE AWARE that it takes several SECONDS to recalculate. I'm off now and will check in again in the morning.

You can save as xlsb ande zip. Did you want this for Googlesheets or Excel. If for GS, I'll stand aside NOW, as I have no idea what GS can and can not do.

[Glenn Kennedy]

It's faster... but still unusable, in my opinion. 1000 teachers is just crippling the sheet.

DV only appplied to the dark pink cells.

[wedzmer]

It's faster... but still unusable, in my opinion. 1000 teachers is just crippling the sheet.

DV only appplied to the dark pink cells.

thanks for all of these man, i really really appreciate it!
and yeah, you're definitely right... it takes a lot of time to load between each DV Cells... I tried the file awhile ago at the office before we lost our internet connection and Excel 2007 almost died. lol
I guess.... I should take this project to rest now... It's not efficient anymore.

[Glenn Kennedy]

I agree.

Unless you can use VBA... you're out of luck. back to the planning phase for you....


You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.