Dash Board

[bilboy]

Hi Friends,


I have this challenge with this dash board. Sample excel attached.


I have two main parameters Mall and Month. For one Mall for a month both revenue and productivity is captured. Similarly data captured for last two months (m1 and m2).


My challenge is creating two drop down (Highlighted in yellow)


1. Select Mall


2. Select Month


Then the trend for revenue and productivity should change accordingly. The same logic should apply when i do different permutation and combination with Mall and Month.


Is it possible, Kindly help!!!!

 

[Luke M]

The trend line shows for two months, but there are only 2 months worth of data. If we change the Month drop down from M1 to M2, and Mall = A, what cells should we be looking at for sparkline?

 

[Faseeh]

Hi bilboy,

see the new table and make spark lines for them:

 

[bilboy]

Thank you friends for the prompt reply!!!

@ Luke: Pls excuse me for creating ambiguity, i have modified the sample excel (attached)

Spark line should change in (J9, J10) for Revenue and (O9, O10) for Productivity when different mall and month are selected.


@Faseeh, The logic needs to work if i include more months or more revenue sub like R3, R4.. Productivity sub like P3, P4..

Hope i was able to clear the query...

Can such a logic be worked out!!!!

 

[Luke M]

I'm afraid I'm still confused. In last upload, M1 is selected, but sparklines show data for M1, M2, and M3??
If M1 is selected, is sparkline a single point (as there's no comparison)? I would then presume M2 shows M1 and M2. But, when I select M3, do I show all 3, or just M2 and M3?

 

[bilboy]

err.. my apologies.., i was having a different thought in mind.. its my fault.. my logic is flawed..

In d new case can i have a logic by which i can get the Spark line (trend) change in (J9, J10) for Revenue and (O9, O10) for Productivity when different mall is selected

The logic needs to work if i include more months m1, m2, m3..or more revenue sub like R3, R4.. Productivity sub like P3, P4..

Again, Thanking in advance

 

[Luke M]

Hi bilboy,

To make it easier to work with the sparklines, we'll setup so formulas to gather our data, and then have the sparklines look at the formulas. Please see attached.

 

[bilboy]

Thanks Luke!!!


Taking forward yesterday logic about multiple drop down i.e. selection of one Mall and one Month (from the drop down marked yellow) and populating the area marked in yellow correspondingly.


Is there a logic that makes this idea possible…!!!


Excel sheet attached..

 

[Luke M]

What type of information are you wanting to populate? It looks like in your new workbook you've removed the dynamic sparklines that I setup before. Were they not right, or something else? What should be displayed for "Current month"? What should be shown for "Change from last month"? A single value, a small sparkline(??)

 

[bilboy]

Respected Luke,

I must really thank you for helping me in asking detail question and trying the problem on own.

Im a beginner and ur support has helped me to explore more functions in excel.

Regarding the problem, im sticking with the simple logic of populating the spark lines as and when data is populated.

Once again thanks a lot!!!!

Warm regards.
Bilboy

 

[Luke M]

Hi bilboy,

Glad to be of help. However, my questions were straight-forward in the reason for asking. I'm not sure what you want to display in the dashboard, and thus can not provide a solution. You will need to answer the questions (and let us know the answers) if you want us to proceed.