(SOLVED) Drop down list removes used cells

**SubwAy** · 10-18-2011, 10:27 AM

Hi guys,
I'm having a bit of a problem with one of my workbooks and after spending hours on the net looking for an answer/tutorial, I finally decided to raise this problem with you. I hope you'll have a solution for me :-)

I have a list of values (C2:C62) on a spreadsheet named "Parameters". I am trying to create a drop down list in a spreadsheet named (Input) for the cells D22:D82 knowing that the values (from the range C2:C62 on "Parameters" can only be used once. What I mean by that, is if you select a value "X" in D22 (on "input"), the value "X" will not appear in the drop down list in D23.

I know that I have to create another list in D2:D62 on "Parameters" to only display the unused values (and I'm guessing this list will auto-update itself after each cell selection) but I just can't figure it out.

From what I've seen on the net, this is definitely doable, without VBA, so I'm hoping one of you will have an answer for me.

Thanks guys!

**Marcol** · 10-18-2011, 10:57 AM

Is this workbook on the lines you are thinking?

No VBa and all named lists are dynamic.

Select from the drop-downs in Column D

**SubwAy** · 10-18-2011, 11:41 PM

Thanks a lot for that.

Does the fact that I;m working with two spreadsheets change anything? I can't get the last column to work, no idea why.

I replicated your formula, with the right column reference, applied it as an array formula and it returns #N/A..... It's driving me mental...

**Marcol** · 10-19-2011, 04:44 AM

It shouldn't make any difference if you are using the list in a different sheet.

I can't see how you can get an #N/A error, if you didn't array enter the formula you would get 0 then NUM#! errors.
However you did correctly confirm the formula.
Can you post a sample of your workbook so I can see the problem?

See the attached, it uses two sheets and has a new formula for named range "List_2", I hadn't allowed for all the values in "List_1" being used up.
This now correctly returns an empty list when all alternatives are used.

**SubwAy** · 10-22-2011, 01:24 PM

Thank you so much!!!!

I'm not quite sure what I did wrong in the first place but I started over, from scratch and everything worked well. I just need to get a bit of help regarding my other thread on the forum and this model should be finished!!!!! After nearly 40 hours of work :-)

Thanks again!