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.
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.
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?
srk11 #REF! errors occur when you reference a cell/range and then delete that cell/range. Did you perhaps delete something?
Look in the formula bar to see which part of the formula cause the #REF! error.