Dynamically Change of Ranked list with change in week (Moving Dash Board)

sarad agarwal

New Member
Joined
Mar 14, 2016
Messages
20
Hi,

I am Trying to create a weekly moving dashboard for ranked values. I have a clue of how can i get the top three ranked validations from a list , but i am not able to link these validations to change with my week.
Source Table:
Week 6Week 7Week 8
Active inactive listing0Active inactive listing1Active inactive listing1
Create listing4Create listing30Create listing10
Create variation relationship1Create variation relationship2Create variation relationship5
Modify existing listing5Modify existing listing3Modify existing listing0
Modify variation relationship1Modify variation relationship0Modify variation relationship0
Unsuppress existing listing1Unsuppress existing listing1Unsuppress existing listing0

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>


Target Table:
Week 9Week 10Week 11Week 12
Top Three ValidationCountTop Three ValidationCountTop Three ValidationCountTop Three ValidationCount
Modify existing listing811 1 1
Create listing40222
Unsuppress existing listing03 3 3

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

The source table will keep adding the weekly values and all the validations that occurred for that week . I want a moving dashboard which gives me data for last 4 weeks from the source table but i want only top three validations based on their count. How can i link the weeks to change dynamically ?
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
week1week2week3week4week5week6
red413834
blue284173
yellow576262
pink797358
green648599
problem statement - obtain top 3 scores for weeks 3 to 6
week1week2week3week4week5week6
red413834
blue284173
yellow576262
pink797358
green648599
798899
687578
576364
current week = week6
3456######
week3week4week5week6
greenredgreengreen
pinkgreenbluepink
yellowpinkyellowred
formula giving green in week 6
=OFFSET(J$16,MATCH(OFFSET(J$16,6,0),J$17:J$21,0),-J$28)
the 6 becomes 7 and 8 for pink and red
the numbers in row marked #### are for simplicity in the formula
you can have ALL ratings for ALL weeks in the bottom table
use an offset match approact to only "dashboard" the last 4 columns

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,305
Messages
5,600,867
Members
414,410
Latest member
4610

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top