Results 1 to 4 of 4

Thread: Dynamic Dependant Dropdown Error

Threaded View

Previous Post Previous Post   Next Post Next Post
  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

    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:	7 
Size:	89.5 KB 
ID:	9931   Click image for larger version. 

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

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
  •