Hide already used value drop down list

[Krypt]

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.

 

[felixstraube]

You can use a helper column (hide it or even on a hidden sheet) with this 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

=OFFSET(Sheet6!$E$2, 0, 0, COUNT(Sheet6!$E$2:$E$100))

Now use =NotUsed in you data validation source.

Data Validation doesn't like the dynamic array formulas. I don't know of any other workaround for now.

 

[Krypt]

You can use a helper column (hide it or even on a hidden sheet) with this 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

=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.

 

[Krypt]

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

 

[felixstraube]

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.

 

[Krypt]

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

 

[felixstraube]

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
	A	B	C	D	E
1	TEL		Used		Available
2	3631		3631		3682
3	3682				3683
4	3683				3684
5	3684				3685
6	3685				3686
7	3686				3687
8	3687
INFO

Cell Formulas
Range	Formula
C2	C2	=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:E7	E2	=IFERROR(UNIQUE(VSTACK(Table1[TEL], C2#),,1),"")
Dynamic array formulas.

And created a named range:
Name: AvailablePhones

=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.

 

[Krypt]

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
	A	B	C	D	E
1	TEL		Used		Available
2	3631		3631		3682
3	3682				3683
4	3683				3684
5	3684				3685
6	3685				3686
7	3686				3687
8	3687
INFO

Cell Formulas
Range	Formula
C2	C2	=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:E7	E2	=IFERROR(UNIQUE(VSTACK(Table1[TEL], C2#),,1),"")
Dynamic array formulas.

And created a named range:
Name: AvailablePhones

=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

 

[felixstraube]

Thanks for the feedback. Glad I could help you.