Hi Mynda,
I need to display the entire questionnaire in the dashboard in one big table so my user see the performance for each property.
I am facing 2 issues while setting this up,
1, My headers are regions and stores under the respective region, age groups, gender. Right now, every column have 2 layers of headers. The users have to select the region then the store one by one. Can you advise me a way to pre-set a filer that allows the second layer of header display all the stores under the same regions?
2, I am using index and match to lookup the each answer's value for regions, age groups and so on. However, this doesn't allow me to return the correct value because several questions' attributes are yes/no or Excellent/Good/Average/Poor/Unacceptable. Do you know other ways to return the right answer.
The ultimate goal is to display the entire questionnaire and have a simple way for the users to compare the stores/age group etc.
BR,
Wen
Hi Wen,
Please provide a sample file. It's difficult to visualise what you're working with and what your desired result is.
Thanks,
Mynda
Hi Mynda,
Sorry for my bad explanation, I attached the file I am working on here for your review.
In "City Performance" tab, cell EA5 is where I control the region and I use offset function in data validation to set up a drop down list underneath the region. As you see now, the users need to select the city, store type in respective region one by one with the drop down list I created. Do you know how I can have all the city, store type listed in row 5 by click the region in cell EA5.
I highlight yes/no in orange and Excellent/Good/Average/Poor/Unacceptable in yellow. I find that all those value is the same because the answers are not unique so the index match function return the first match but not the question I refer to.
BR,
Wen
Hi Wen,
You have these problems because the source data is not in a format that supports the formulas. The first rule of Excel is to put your data in a Tabular format. This enables you to use the tools in Excel the way they were designed.
In the attached file I used Power Query to unpivot the data layout and then summarised it in a PivotTable. There are Slicers that allow you to choose the City and Store Type.
Note: the Count & Percentage columns could possibly be removed from your source data and recalculated using another PivotTable. You'll see they are values in the City and Store Type slicers, which doesn't make sense, but I've left them there for completeness.
You can rearrange the order of the questions by left-clicking and dragging the outer edge of the cell and releasing it in a new location. A green marker will indicate where it's going to be inserted. Likewise for the answers.
Mynda
