DeletedUser
New member
- Joined
- Jul 27, 2020
- Messages
- 3
- Reaction score
- 0
- Points
- 0
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.
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.