Hide already used value drop down list

Krypt

New Member
Joined
Apr 17, 2023
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

Hopefully someone can help me with my problem.
Problem is :
When i select any value in dropdown range B7:B2 it must hide the value in the dropdown list, what I managed to do with this formula : =FILTER(Tel,COUNTIF('S.A.B.C.A. Cell Phone Mitel'!B7:B21,Tel)=0) on capture4.
This formula is on another sheet "INFO"
Can we when a specified value ("OK") is entered in Range I7Ii20 sheet cell phone Mitel that the hided value in the dropdown reappear again in the dropdown list.
I did multiple test with Isblank, count, if(s), or, and, without any result.
I hope there is enough info to solve my issue.

Thanks in advance for the help,

Kind regards Krypt.
 

Attachments

  • Capture4.JPG
    Capture4.JPG
    33.4 KB · Views: 11
  • Capture1.JPG
    Capture1.JPG
    61.7 KB · Views: 11

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
You can use a helper column (hide it or even on a hidden sheet) with this formula.

Excel Formula:
=FILTER($A$2:$A$8, NOT(COUNTIF($B$2:$B$8, $A$2:$A$8)))

Let say it is located in E2 on the same sheet.
Then you create a named range like this:

Name: NotUsed
Excel Formula:
=OFFSET(Sheet6!$E$2, 0, 0, COUNT(Sheet6!$E$2:$E$100))

Now use =NotUsed in you data validation source.



1723484604860.png


Data Validation doesn't like the dynamic array formulas. I don't know of any other workaround for now.
 
Upvote 1
You can use a helper column (hide it or even on a hidden sheet) with this formula.

Excel Formula:
=FILTER($A$2:$A$8, NOT(COUNTIF($B$2:$B$8, $A$2:$A$8)))

Let say it is located in E2 on the same sheet.
Then you create a named range like this:

Name: NotUsed
Excel Formula:
=OFFSET(Sheet6!$E$2, 0, 0, COUNT(Sheet6!$E$2:$E$100))

Now use =NotUsed in you data validation source.



View attachment 115302

Data Validation doesn't like the dynamic array formulas. I don't know of any other workaround for now.
Thanks for the feed-back Felixstraube, i will try it out.
 
Upvote 0
Hi Felixstraube,

Thanks again for the reply, but it did not solve my problem.
As mentioned earlier, I need to reuse the data used in the dropdown list of sheet 1 column A (TEL numbers, 3631, 3682, ....) once they have returned the device.
All changes are to do on another sheet where all my data is stored of used phones
The data should return in dropdown list based on a data in column I of sheet1 with value ‘OK’.
I have been working on this for a very good while and know this is difficult.

Kind regards,
Krypt
 
Upvote 0
I'm really confused. I don't understand the whole process you need.

Range B2:B7 doesn't have any data it seam.
Where is the range L7:LI20?
On what condition do you want the values to hide and "reappear"?
What does "once they have returned the device" mean? Who returns what device, when?

Could you explain it in more detail?

Maybe share the file or a sample with XL2BB.
 
Upvote 0
I'm really confused. I don't understand the whole process you need.

Range B2:B7 doesn't have any data it seam.
Where is the range L7:LI20?
On what condition do you want the values to hide and "reappear"?
What does "once they have returned the device" mean? Who returns what device, when?

Could you explain it in more detail?

Maybe share the file or a sample with XL2BB.
Hey Felixstraube,

Sorry for the confusion and misunderstanding.
The problem is that I cannot download XL2BB properly on my company laptop and certain features are blocked.
I have posted a new image with, hopefully, the proper explanation for what I want to accomplish.
Thanks again for the return.

Picture 1 "test1" is sheet with data en picture 2 is sheet with formule.

Hopefully this will help.

Kind regards
Krypt
 

Attachments

  • TEST 1.PNG
    TEST 1.PNG
    63.8 KB · Views: 9
  • TEST2.PNG
    TEST2.PNG
    48.5 KB · Views: 9
Upvote 0
Thanks for the response and clear explanation. Now I understand the problem.

I added another column to you INFO sheet if that is ok:

Phone.xlsx
ABCDE
1TELUsedAvailable
2363136313682
336823683
436833684
536843685
636853686
736863687
83687
INFO
Cell Formulas
RangeFormula
C2C2=LET( m,'S.A.B.C.A. Cell Phone Mitel'!A7:A1000, returned,'S.A.B.C.A. Cell Phone Mitel'!I7:I1000, IFERROR(FILTER(m,(returned<>"OK")*(m<>"")), "") )
E2:E7E2=IFERROR(UNIQUE(VSTACK(Table1[TEL], C2#),,1),"")
Dynamic array formulas.


And created a named range:
Name: AvailablePhones
Excel Formula:
=OFFSET(INFO!$E$2, 0,0, COUNTA(INFO!$E$2:$E$1000))

Then in sheet "S.A.B.C.A. Cell Phone Mitel" we use that named range for the datavalidation.

Here is the working file to download: Phone.zip

Let me know if this works for you.
 
Upvote 1
Solution
Thanks for the response and clear explanation. Now I understand the problem.

I added another column to you INFO sheet if that is ok:

Phone.xlsx
ABCDE
1TELUsedAvailable
2363136313682
336823683
436833684
536843685
636853686
736863687
83687
INFO
Cell Formulas
RangeFormula
C2C2=LET( m,'S.A.B.C.A. Cell Phone Mitel'!A7:A1000, returned,'S.A.B.C.A. Cell Phone Mitel'!I7:I1000, IFERROR(FILTER(m,(returned<>"OK")*(m<>"")), "") )
E2:E7E2=IFERROR(UNIQUE(VSTACK(Table1[TEL], C2#),,1),"")
Dynamic array formulas.


And created a named range:
Name: AvailablePhones
Excel Formula:
=OFFSET(INFO!$E$2, 0,0, COUNTA(INFO!$E$2:$E$1000))

Then in sheet "S.A.B.C.A. Cell Phone Mitel" we use that named range for the datavalidation.

Here is the working file to download: Phone.zip

Let me know if this works for you.
Hello Felixstraube,

your formula works perfectly.
It's what I was looking for, this is going to make my job very much easier .
Big thumbs up to you for solving my problem.
Wish you a nice day.
King regard,
Krypt
 
Upvote 0

Forum statistics

Threads
1,224,814
Messages
6,181,128
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top