Link Data to Excel Dashboard (Same Document)

[TechSlinger]

Hello

I have a dashboard created in Excel 2010 (file extension is xlsm). It has the 12 months of the year for 10 different metrics. Conditional formatting compares the monthly results to the target cell and shows red or green depending on the results. I now want to incorporate performance measurement tracking but not sure how to do it.

What I envision (but don't know how to execute) is that the user would put the cursor in the cell they will be creating an action plan for. Then they push a button and a data entry form pops up with the metric name, month of activity (there is a row indicating month across the top) and the metric value pre-filled. It also has a place to enter various Action Plan information. Then the form can be closed and the information from the form is saved to a hidden table. Then the cell that has an action item would have the border change colour so that users would know that an action plan exists for that month for that metric and could access it.

Can anyone help get me started with the code or plan for execution that I would require for this? Thanks very much.

 

[SkipVought]

Hi again,

Have you designed the hidden table, the data entry form?

If so what is table structure?

What column is the metric name?

WhT row are the dates?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TechSlinger]

Hi Skip

Thanks for responding. No I haven't created anything yet, was first asking about approach to then know how to proceed.

My thought about table structure is that I would have one column per requirement of the Action Plan, with most being text/memo fields. This is to house the data but it wouldn't make sense to view it in this fashion. Fields include:
metric name
current performance (derived from cell the cursor is in)
target performance (derived from cell in column C
prior performance (derived from cell previous to current)
rationale: memo field entered by user from Form
action plan steps - fields 1 thru 5 all entered by user and are text
comments - memo field entered by user

Metric Names are included in column B but each has their own cell (due to formatting, the cells won't be continuous). For example, metric 1 name is in cell B9, metric 2 is B13 etc. The same would be true of the target cell above i.e. metric 1 target cell is C9, metric 2 is C13..so whatever row B is then also C is.

The dates are contained in the range E8 to P8.

Another thing I was thinking is that if the user opened the form on a cell that already has an entry, then the entry needs to show, otherwise it is empty to accept data.

Thanks for any assistance you can give me, Skip.

 

[TechSlinger]

Hi

So is anyone able to help with this query? Thanks very much.

 

[SkipVought]

So I was really confused and I got sidetracked.

So, you have these Column Headings
[tt]
metric name
current performance
target performance
prior performance
rationale:
action plan steps
comments
[/tt]
metric name is in column B and dates are in columns E
Where do all these other headings go?

Tell you what. Respond to this FAQ faq68-5829 and I'll get an eMail from Tek Tips that includes your eMail address. I'll respond and get a copy of your workbook so we I talk intelligently.

Why do the headings occur in row 8? What's above your table?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TechSlinger]

Hi Skip

I responded to the post as you requested but haven't heard back. Are you still able to assist? Thanks.

 

[TechSlinger]

Hi Skip or anyone else

Anyone able to assist me with this? Thanks.

 

[TechSlinger]

Yo Skip, are you able to help? I'd really appreciate any assistance you can provide. Thanks.

 

[SkipVought]

Sorry. I just retired and now I'm busier than ever, with a lot less time to devote to questions like yours.

Perhaps some other member could help.

In the mean time, look at VBA HELP on events. There is a Worksheet_SelectionChange event. That could be used as a trigger to start the process.

I check in on TekTips briefly at morning, midday & evening. I can handle some quick hitters, but nothing that would take prolonged interaction. Sorry.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TechSlinger]

No problem, Skip, thanks for letting me know. And Happy Retirement!!