Help with the KPI Scroll Chart

[Dyson]

Hello all.


Now I admit I am not the greatest on Excel but I am trying to learn new things every day. I have stumbled upon the 6 part KPI dashboard and I am already stuck on the first part which is making a scrolling chart.


I have my date in sheet 1, as instructed I then make a 10 row table in a new sheet.


I have added my scroll bar but then the trouble begins. When I go into format control in the scroll bar and try to add Calculation!$D$5 it comes back with an error message!


I am also unsure about the formula =OFFSET, I presume that when I am using this formula the part which is Data!E5 the E5 in question needs to be on the sheet with my data in.


I understand that people will read this and think I am just thick, trust me I really do envy you guys who are wizards at this kind of thing.


If anybody would be so kind to maybe give me some basic pointers or some more advice, I can’t begin to tell you how thankful I would be.


Keep up the good work guys!


Kind regards


Alex

 

[Hui]

Dyson

Instead of typing in the formula try using the Small Cell Selector Icon (on the right of the address box), and navigate to the cell and select it. You may have a Space or mis-spelt word somewhere


For the Offset part the "Data!E5" is referring to the Data Page, if your E5 is elsewhere change the reference to 'My Sheet Name'!E5

 

[Dyson]

Hui


Thanks for your speedy response, it is really apreciated.


My data is on sheet 2 (all in the same workbook) I have just tried the following formula =OFFSET(Data! Then I click on cell E5 in my data sheet....so far so good. I then continue the formula with Calculation part, now what is confusing me is this, on your demo your formula ends with Calcualtion!$D$5,0) I cant get this part of the formula for love nor money.


Is the $D$5 in question on the data sheet or on the sheet with the table and scroll bar. Either way if I type it in I just get an error message. I really am trying and making this my mision now.


I cant thank you enough for your time.

 

[Hui]

Sheet 2 will need to be as 'Sheet 2'!$D$5 including the quote marks because it has a space

 

[Dyson]

Hui,


Is it possible I can send you what I have got so you can show me where I have gone wrong? I am just getting myself frustrated, I have been trying this for 2 days, how embarrasing.


Thanks

 

[Hui]

Dyson

Can you post it somewhere

Look at http://chandoo.org/forums/topic/posting-a-sample-workbook


No need to be embarrased, we all get stuck from time to time

 

[Dyson]

Thanks for your help!

 

[Dyson]

Hui......I DID IT......Praise the lord.


Another little problem (nothing major)

I am currently linking a line graph from my KPI Scroll Table which includes dates, however the dates are not continuous. In the selct data for the table I have Selected from B5,B9 thats fine and the chart scrolls nicely.


Trouble is we dont work weekends so these dates are missing from my KPI chart, but the Line graph puts the weekend dates in!


Any ideas.


Thanks a million.