View original (French)

Dropdowns - space issue

Solved
touroul Posted messages 520 Registration date   Status Member Last intervention   -  
touroul Posted messages 520 Registration date   Status Member Last intervention   -

Hello forum

I need a little help.
In the Excel file below:

I would like to create dropdown lists of Versions (in the Main sheet, column B) dependent on the input in column ITEMS (column A)

The Versions in question are found in the Versions sheet.

But here's the thing: I have spaces that I can't remove in my ITEMS (database source).

And named ranges cannot contain spaces.
I consulted an AI that suggested a data validation list in the form =INDIRECT(SUBSTITUTE($A4," ","_")) which theoretically allows me to replace the space in my items with an underscore (the one from my named list).

Here is how I construct my adaptive named range: =OFFSET(Versions!$C$1,1,0,COUNTA(Versions!$C:$C)-1,1)

But it doesn't work, I don't understand why.

Can I ask for a little help?

Here is my sample file:
https://cijoint.org/r/Qu6MveLc#CBl/8khVs5AlmFu4meVMZAVUzj0w5dHquIwJc6YTDxM=

Thank you in advance for your ideas.


5 answers

Answer 1 / 5
danielc0 Posted messages 2173 Registration date   Status Member Last intervention   285
 

Hello,

A formula without named ranges:

 =OFFSET(Versions!$B$2;;MATCH(A4,Versions!$C$1:$AZ$1,0),COUNTA(OFFSET(Versions!$A:$A;;MATCH(A4,Versions!$1:$1,0)-1))-1)

https://1fichier.com/?gkeimfv42gc8gv7ac9lo

Daniel
1
Answer 2 / 5
danielc0 Posted messages 2173 Registration date   Status Member Last intervention   285
 

Hello,

I adapted the formula and the arrangement of your data:

 =OFFSET(Versions!$A$2;;MATCH(O4;Versions!$1:$1;0)-1;COUNTA(OFFSET(Versions!$A:$A;;MATCH(O4;Versions!$1:$1;0)-1))-1)

https://1fichier.com/?w23lrp1mojzm8bbk75ww

Daniel
1
danielc0 Posted messages 2173 Registration date   Status Member Last intervention   285
 

If you have Excel 365, the formula can be simpler:

 =FILTER(OFFSET(Versions!$A$2:$A$1000,,MATCH(O4,Versions!$1:$1,0)-1))

Daniel

1
Answer 3 / 5
touroul Posted messages 520 Registration date   Status Member Last intervention   16
 

Hello Daniel,

A thousand thanks for the time you dedicated.
Indeed, it works perfectly, I will be able to adapt it to my file.

Bravo and once again, all my gratitude.
0
Answer 4 / 5
touroul Posted messages 520 Registration date   Status Member Last intervention   16
 

Hello Daniel

I'm having some trouble adapting the formula to my original file.

I added a few items (on an orange background), but unfortunately the lists are empty.
Here is a file that is closer in format to the original.
The "Versions" list can go up to column DZ.

Here is the file:

https://cijoint.org/r/UuWFmmoD#RX7opohhG2iZj3+tE/fwvauxj4q+uvYggkGEOaNB6qE=

Thank you in advance for the ... after-sales service.
0
Answer 5 / 5
touroul Posted messages 520 Registration date   Status Member Last intervention   16
 

Hello again Daniel

Thank you once again, your two formulas work perfectly in my project.
I chose the second one because it seems to use less computational power over the length of the table, and I am on Excel 365.

I've been using named ranges for years, but your solution doesn't rely on them and it greatly simplifies additions.
A thousand thanks for the time you spent for me and have a good Sunday.
0