• 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

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

Last edited by a moderator:

bosco_yip

Excel Ninja
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

Last edited:

Hany ali

Member
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

Last edited by a moderator:

bosco_yip

Excel Ninja
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:

bosco_yip

Excel Ninja
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
 

bosco_yip

Excel Ninja
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
 
Top