I am a new user and I am trying to create drop down list to use as part of a dashboard.
I am able to create drop down for region using name range and data validation but everything I have tried to make the Region link to the respective areas within comes back with error. Attached excel file.
Please help!! It would be greatly appreciated!
Make all Areas named ranges:EXCEL will replace blanks with underscore
For DV
=INDIRECT(SUBSTITUTE($D$1," ", "_"))
Please try
Note : Cell H3 have a space after 'South East'. And all dropdown must be continuous (no blank cell).
Please Login or Register to view this content.
Note2 : You may change dropdown list of D1 to $A$13:$I$13 for avoid wrong spell of region.
Regards.
Last edited by menem; 09-06-2021 at 10:42 PM. Reason: add more note.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
Thank you both this is excellent!
How can I now link these names to corresponding data within Pivot table.
Frances
This works perfect on the sheet attached but when I move this to a different worksheet it doesnt work, have you any ideas of how to fix this?
Post the error file. I copied the C1:D2 range to another sheet without any problem
Please can you help.
I want the dropdown buttons located in the dropdown tab to work in the dashboard tab but this doesnt work for me.
How then can I link the drop down matrix with get pivot function?
Help much appreciated.
Thanks
Try
=OFFSET(Dropdown!$A$13,1,MATCH($B$2,Dropdown!$13:$13,0)-1,COUNTA(OFFSET(Dropdown!$A$13,1,MATCH($B$2,Dropdown!$13:$13,0)-1,1000000,1)))
Much easier if named ranges had been used.
You need to reference the Dropdown Sheet in the formula.
My interpretation:How then can I link the drop down matrix with get pivot function?
Created named range DBLA based on E2 in Dashboard
in "Get Pivot Data"
in B2
=DBLA
in B5
=GETPIVOTDATA("Sum of Smoking Prevalence in adults (18+)%",'Pivot table'!$A$3,"AreaName",$B$2)
Repeat for other
Last edited by JohnTopley; 09-28-2021 at 12:48 PM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
John,
Thank you so much this is super.
Thanks
Frances
You're welcome.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED
No problem I will do that.
Is it possible to display different selected values based on the dropdown options.
Can you display only the values for option selected in B2? (no local area option selected)
Currently it is showing the local area option selected in E2.
You have been such a help to me.
Your duplicate thread has been closed - continue here, please.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If you want to display a Region then we need to be able to identify it is Region not Area selection: for example the "Area" dropdowns have a blank entry so we can add a conditional statement in the "Get Pivot Data" sheet
Blank is probably not a good choice as it delimits the Area dropdown lists: so another choice is required.
Last edited by JohnTopley; 09-29-2021 at 06:04 AM.
See attached.
Added drop down to "Get Pivot Data" to select "Region" or "Area"
in B2
=IF(A2="Region",DBR&" Region",DBLA)
DBR is named range for Region on "Dashboard"(updated file as I initially used "Dropdown!)
Last edited by JohnTopley; 09-29-2021 at 06:48 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks