Results 1 to 4 of 4

Thread: Dynamic Dependant Dropdown Error

  1. #1
    Neophyte eannab786's Avatar
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    3
    Articles
    0
    Excel Version
    Office Home And Business 2019

    Dynamic Dependant Dropdown Error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	F9 Results.jpg 
Views:	3 
Size:	89.5 KB 
ID:	9931   Click image for larger version. 

Name:	Formula and Result.jpg 
Views:	2 
Size:	93.6 KB 
ID:	9932  
    Attached Files Attached Files

  2. #2
    Neophyte eannab786's Avatar
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    3
    Articles
    0
    Excel Version
    Office Home And Business 2019
    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.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,781
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by eannab786 View Post
    For anyone with a similar problem, this issue has now been resolved.
    Yes, at https://www.mrexcel.com/board/thread...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.
    Last edited by p45cal; 2020-07-27 at 01:44 PM.

  4. #4
    Neophyte eannab786's Avatar
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    3
    Articles
    0
    Excel Version
    Office Home And Business 2019
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •