Log In
Register
Home
December 7, 2020
Offline
Hi
I am having trouble on the below formula.... need a fresh pair of eyes. It for a drop down data validation list.
I keep getting an error message keeps saying that a "Named Range I specify cannot be found" but they are all there... I am so confused. I have checked and double checked again in Name Manager and the tables are all there and all spelt correctly. where am I going wrong... besides the fact that my formula my be incorrect.
and I have checked my tables in the Name Manager and they are all scoped for the workbook.
=INDIRECT(IF(D3=“Corporate”, “tbl_Non”, IF(D3=”Specific Permit/Facility Documentation”, tbl_Permits, IF(D3=“Multiple Permit/Facility Documentation”, “tbl_Multi”, “tbl_Facility” ))))
based on the value in cell D3, I want it to return the corresponding table in E3 as a drop down. The above didn't work and I don't understand why.
my Table Names are correct.
and the dropdown values in D3 are correct and correspond correctly.
I did a nested IF function, am I using the incorrect function?
thanks
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Seems that you get an error if D3 = Specific Permit/Facility Documentation, else it should work. Is that correct?
What I see you are missing in your formula is a pair of quotation marks, highlighted in red below.
=INDIRECT(IF(D3=“Corporate”, “tbl_Non”, IF(D3=”Specific Permit/Facility Documentation”, "tbl_Permits", IF(D3=“Multiple Permit/Facility Documentation”, “tbl_Multi”, “tbl_Facility” ))))
Br,
Anders
December 7, 2020
Offline
Hi
thanks, but that is not the problem... I just omitted the quotation marks above when typing, but I tried again just in case, and still get the error message "A named range you specified cannot be found".
the below formula should work. What am I missing?
=INDIRECT(IF(D3=“Corporate”, “tbl_Non”, IF(D3=”Specific Permit/Facility Documentation”, "tbl_Permits", IF(D3=“Multiple Permit/Facility Documentation”, “tbl_Multi”, “tbl_Facility” ))))
Trusted Members
February 13, 2021
Offline
Could you upload a sample worksheet that shows your problem. It would be easier to help troubleshoot your issue if we can see what you have done in the workbook.
December 7, 2020
Offline
Hi Jessica
thanks for your reply. Sample worksheet attached.
on the "CPB Doc Request" tab, in E3 I need the drop down to list the correct corresponding table from the value in D3.
If D3 = "Corporate" then table "tbl_Non"
If D3 = "Specific Permit/Facility Documentation" then table "tbl_Permits"
IF D3 = "Multiple Permit/Facility Documentation" then table "tbl_Multi"
otherwise, if any other value in D3, then table "tbl_Facility"
these dropdown lists are in worksheet tab "CPC_Corp_Spec_Multi_Ops_Skid"
thanks so much for having a look.
Trusted Members
February 13, 2021
Offline
I got the indirect function to work, it looks like there are spaces in your formula, maybe that's your issue? Either way, attached workbook, hope it helps. 🙂
Answers Post
December 7, 2020
Offline
oh that's great, it is working perfectly now ... is that all that was wrong with it, just some spaces..... doh!! so fustrating.
thanks for taking a look, sometimes its just a fresh pair of eyes.
thanks Jessica!
Kind Regards
1 Guest(s)
