How to make a dynamic guage and chart

[Former Member]

Hi guys, first i want to thank this helpful forum that every information helped me in my project

i'm a new user in xcelsius and i'm working on KPI project i want to know how is the same gauage and chart changes the reading according to selected button (as shown in the picture).

what i made nw is many numbers of charts and gauges behind each other and each one has a dynamic visibility but it's a stupid way

can any one help me

The program file

http://www.megafileupload.com/en/file/381442/Final-Test-rar.html

[Former Member · Accepted solution]

What you want is to add all your data somewhere in a bit table on your spreadsheet (ideally on a separate worksheet).  Something like:

code | KPI | Factory | Type | YTP Value | January | February | March | etc.

where "code" is a new parameter we'll use to sift through this data.  Fill it up using

A1 = B1 & "_" & C1 & & "_" D1

Then, on your main worksheet, use VLOOKUP() to find the right values.

So map your KPI radio button to A1, the Factory to A2 and the Type to A3.  Then, for example, the value for your gauge will be mapped to B1 where

B1 = VLOOKUP(A1 & "_" & A2 & "_" & A3 , A1:Z1000, 5, false)

and use similar formulas (replacing 5 with '6' for January, '7' for February, etc.) for the other charts.

This way you a single gauge chart for everything.

[Former Member · Accepted solution]

Thanks for your help but i'm a new user for this program. i can't imagine the sheep of the spread sheet can you give me an example or u can find my excel sheet

thanks again

[Former Member · Accepted solution]

Sure.  Here are the formulas and setup:

1. UI.  Yellow means they're the user input (radio buttons and drop-down menus) and orange are the output (for chart and graphs)

2. Raw data.  Should be straightforward to modify to suit your needs.

[Former Member]

the main problem that i'm not good at excel so i couldn't do what u wrote in ur first reply. And can't do a specific cell that the gauge and the chart read from it to change with the value. you can send me your mail on amr.raouf.azab@gmail.com to send you the program to clear the situation

[Former Member]

did you check it ?

[Former Member]

Honestly I'm not sure what you're still having trouble with.  Maybe this will help: it will grab the correct value for the chosen KPI.

[Former Member]

[Former Member]

i don't have a problem with this issue i already did visibility for each KPI but i don't understand how to make in a spread sheet and don't get what u said in the last comment

[Former Member]

Can you post a screenshot of what your data looks like?

[Former Member]

No i mean that the radio buttons shows according to the type which selected from the combo box, can you give me you mail to send u the file

[Former Member]

Oh I see.

So the graphing and everything can remain unchanged.  Now what you want to do is add another input table, for the list of KPIs.  Something like:

Type, KPI1, KPI2, KPI3, KPI4, etc.

So the first row would look like:

someType, OTDS, PRR, DPM, etc.

and so on for the other rows.

Then set the 'Type' combo box's Insertion Type to 'Row' and map it to this table.  Set the output of the insertion to, say, A1:J1, and map A1:J1 to your radio button labels.

So now, when you select a type, the options for the ratio buttons will show up in this row.

[Former Member]

Ok it's seems more clear now, i hope if u can open my file to know what i'm going to do exactly, every Type has its KPI shown when the type chosen, so hwo to connect the dynamic visibility cells between the two sheets ?

[Former Member]

Hi Amr,

I'm not sure what you mean.  Are you saying that you sometimes have more than 1 YTD gauge showing at the same time?

I can't open your file due to restrictions on my end.