Hi there everyone,
I am a beginner-intermediate level excel user, and I am attempting to build a feedback sheet to record, and eventually analyse, feedback from the public on services provided by local leisure facility.
In the attachment provided, I require help in the tab labelled "Jan", and the section marked "Type of Feedback". I am using 4 drop down menu's to select the appropriate feedback based on: "Department">"Type of Feedback", (Positive, Negative, Other)>"Subject"> and "Specifics".
My issue comes as the facility has 5 departments - Aquatics, Customer Support, Fitness, Kauri Kids, and Recreation - and some departments receive similar forms of feedback, (Positive Customer Service vs Negative Customer Service, et cetera).
I am looking for help with a formula to use in data validation to enable me to select different departments feedback datasets that are similarly named.
I am currently using Named Ranges, and the formula, "=INDIRECT(LEFT($I7,1) &"_" & SUBSTITUTE($J7," ","_"))", to select the correct data for the drop downs, which has worked up to creating named ranges/selecting data for "Fitness", as it shares many similar Feedback Type/Subject's as Customer Support, and I expect a similar problem with the remaining departments.
I suspect that a formula variation of the one I am already using would work, however tweaked so that it can differentiate between departments by reviewing the first 2 letters of a named range, (i.e. for negative feedback for Fitness, "FN_Customer_Service vs negative feedback for Customer Support, CN_Customer_Service), however I am not sure how to do this and my experimenting is hitting a roadblock.
Any help with creating such a formula, or an alternative method that I could use, would be greatly appreciated.
Thank you for taking the time to read, and hopefully help me with, this, and have a lovely day.
Bookmarks