• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Make the Range of the other drop-down list dynamic and without spaces

Hany ali

Active Member
Hello every One
I want to help my professors in making the Range of this equation dynamic and without Spaces so that when choosing lists of Areas I see all items without spaces from Data Validation
Code:
=INDEX(Data!$B$2:$Y$46,,MATCH($B3,Data!$A$2:$A$42,0))
 

Attachments

  • drop-down list.xlsm
    13.5 KB · Views: 4
Last edited by a moderator:
please i want to look for this Photo to be Clear and i Want
Dynamic range
 

Attachments

  • Untitled.png
    Untitled.png
    127.6 KB · Views: 7
Last edited by a moderator:
Try,

1] In "Data" sheet, existing range B2:E21 shift down one line to B3:E22

2] In "Data" sheet, B2, formula copied across :

=IF(B1="","",MATCH("zzz",B$3:B$1000))

3] In "Drop-down menu" sheet, select C3 >> Data Validation >>

>> Allow : List

>> Source : =OFFSET(Data!$A$3,0,MATCH($B3,Data!$A$2:$A$46,0),OFFSET(Data!$A$2,0,MATCH($B3,Data!$A$2:$A$46,0)))

>> OK

Then, copied down.

Regards
Bosco
 

Attachments

  • Drop-down list in remove blank.xlsm
    15.6 KB · Views: 7
Last edited:
please mr.Bosco
Which equation instead of this equation for Arabic texts ?
[CODE]=IF(B1="","",MATCH("zzz",B$3:B$1000))[/CODE]
because this one not work with me in case Arabic Texts
and elso when I Used this one

Code:
=IF(B3="","",LOOKUP(2,1/(NOT(ISBLANK(B$5:B$1000))),ROW(A:A)))
i show Data Validation but with space ,i want without Spaces
 

Attachments

  • Untitled.png
    Untitled.png
    115.7 KB · Views: 4
Last edited by a moderator:
please mr.Bosco
Which equation instead of this equation for Arabic texts ?
Code:
=IF(B1="","",MATCH("zzz",B$3:B$1000))
because this one not work with me in case Arabic Texts

I don't know above Arabic text, you could try to use the Greek letter Omega "Ω" instead.

=IF(B1="","",MATCH("Ω",B$3:B$1000))

or, this one,

=IF(B1="","",MATCH("*",B$3:B$1000,-1))

Regards
Bosco
 
Last edited:
thanks alot for your Replay ,just this eguation work
Code:
=IF(B1="","",MATCH("*",B$3:B$1000,-1))
but still have space for some Data Validation and Some Missing For Another
 

Attachments

  • Data Validation.xlsm
    13.2 KB · Views: 2
  • Untitled.png
    Untitled.png
    131.2 KB · Views: 4
Unable to read your Arabic text file, please converted to English file

It seem your file layout is appeared different from your post #.01, so your formula maybe amended not correctly

Regards
Bosco
 
sorry mr. Bosco
may be because Now in the Same Sheet
 

Attachments

  • Data Validation2.xlsx
    13 KB · Views: 4
Last edited:
Just change your Data Validation formula in C2 (with highlighted in red color)

From this :
=OFFSET($A$5,0,MATCH($A$2,$A$4:$A$100,0),OFFSET($A$4,0,MATCH($A$4,$A$4:$A$100,0)))

Into this :
=OFFSET($A$5,0,MATCH($A$2,$A$4:$A$100,0),OFFSET($A$4,0,MATCH($A$2,$A$4:$A$100,0)))

Regards
Bosco
 
Back
Top