Forum Discussion
Dependent Dropdown list With Filter Function
Nevermore91 See attached. Some tricks are needed but I trust you will be able to replicate in with your real data.
thanks a lot tho
Sorry but I don't understand. What row data is it that you don't want to change?
Riny_van_Eekelen I don't want to transpose the data. I want to work on column A and B as is without transposing them. Also, in your provided example, I will have an issue if a 4th vendor is added. I will have to update the range in the xlookup from F3 to F4 and go through everything again which does not help unless if there is a workaround that.
Nevermore91 To avoid having to manually add new entries you can point the XLOOKUP at entire columns, but preferably to a large enough range, let's say 10000 rows. Assuming that you will not have more than 10000 vendors. The formula in E will spill down automatically if you add more vendors. Then drag the formula I have in F far enough down.
Change the DV formula to =XLOOKUP(B11,$E$1:$E$10000,$F$1:$F$10000)# and it will work just the same. In case you prefer entire columns us this: =XLOOKUP(B11,$E:$E,$F:$F)#
Still don't understand your problem with transposing data. What does it matter? You can even put the formulas in a different sheet so that they disturb any format/lay-out. But you need some way to create the lists that source the data validation.
- Hi Riny,
I came across your solution whilst searching my problem on Google. Your solution worked perfectly well an hour ago. For some reason, now I'm getting a #REF! error. I've not changed the formula in any way. It's rather bizarre. Any common reason you know of why a #REF! error would be generated on this formula?
