Dynamic Dependant Dropdown Error

Hi All,

First time poster, long time reader. I have been having an issue with a formula I am inputting into the data validation widget to create a dynamic dependant dropdown error. The formula is as follows:

=OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,COUNTA(OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,20,1)),1)

When the formula is put into the "Data Validation" widget, it returns the "There's a problem with this formula" error.

Normally this would make sense and indicate that there was an error with the formula, but when I put the formula directly into the cell to test it, I can see the results. Although, admittedly, the results return a "#VALUE!". However, when i select the formula in the cell and press F9, it returns the desired result.

I have attached both the workbook (the tab in question is "Expense Input" but it references the tab named "Setup".

Any ideas or help would be greatly appreciated.

Hi All,

For anyone with a similar problem, this issue has now been resolved. The problem was the reference to table ranges in my formula when using data validation.

eannab786 said:

For anyone with a similar problem, this issue has now been resolved.

Click to expand...

Yes, at https://www.mrexcel.com/board/threads/dynamic-dropdown-list-error.1141243/


u/The_Dino_Nugget/eannab786, as a 'long time reader' I'd have expected you to be aware of the rules at nearly all these forums about cross posting (merely that you need to supply links).
Thank you for alerting us that the problem has been solved.

Hi p45cal,
Thank you for informing me of the rules. I was aware of them, but you are absolutely right, I overlooked the links in the original post (on the other forums as well). My apologies for that. I will make sure that it does not happen again.