Dashboard Advice Please

[Welshbird]

I have been producing a 'dashboard' in Excel 2003 for most of this year.

I need to be able to provide the graphs by email each morning but not the data behind them.

Currently I just have 4 sheets in my chart dashboard which reference the main data spreadsheet. Now, the data source for the chart fails if the link is more than 32 characters so each spreadsheet is stored locally to me.

Generally this works OK - but there are times when it is um... flaky. Sometimes the charts don't all update. And generally, having to keep these things locally to me isn't ideal as they aren't accessible to anyone else.

I'd appreciate ANY advice here - I'm at the end of all of my ideas.

Thanks Guys.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

hi,

I maintain sever large workbooks that are used to report to various people who only want to see the charts. I simply copy the sheets containing the charts and other relevent data to a new workbook, turn the calculation to MANUAL, save on the network and send the users a link.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Thargy]

Pish, tush,

obviously you need to use a database in there somewhere.

<Wanders off muttering>

Regards

T

 

[Welshbird]

Tharg - I agree, but the big boss wants a spreadsheet as that means if she hovers her mouse over the chart she can see any figure she chooses.

And if the big boss wants, the big boss gets!

I'm going to do some more research, but any other ideas would be thankfully received!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Tha accountant geezer that I sit next to has suggested the following:

Have a data sheet within the chart workbook that has links to the relevant bits of the detail sheet, and simply run the charts from that data rather than the other worksheet. Reason being that the 32 character limit only applies within charts, not within data links.

I'm going to have a test but seems logical to me at least.

Thoughts anyone?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[xlbo]

Hi Fee - Sounds reasonable - there's about a thousand ways of doing what you are doing (dashboards in excel) however and the best way will probably be dependant on your specific situation....capability with simple VBA could make this pretty straightforward although it seems like you have what is the most straightforward solution anyway (if what you posted is yur only issue with the dashboards)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Welshbird]

OK. I added a sheet with my data which is linked to the big Excel 'database' and I run charts of that.

Now on the network this looks fine - but when I email out to users it looks rubbish and somehow seems to 'lose' its links. Calculation is set to Manual too.

At present I'm printing all to PDF and then emailing; but it isn't really what we want - just a temporary fix.

Help anyone? I'm starting to leak bits of brain out of my eye sockets......

(And yes Thargy - What I need is a database and a reporting system. But it ain't gonna happen!)

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

but when I email out to users it looks rubbish and somehow seems to 'lose' its links

When you say LINKS, are you referring to cell links or database links? In other words, HOW are your workbooks linked?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

They are cell links.

Basically I have one huge spreadsheet that is (pretending to be) a database. This records every individual sale by day to every centre. Now, a centre never orders more than once a day, and there are < 20 centres. Oh, this is split into four products and two countries.

This spreadsheet uses TODAY() to sum the sales once TODAY is < the date for that column. If this isn't yet yesterday (as it were) then it uses NA() as then this doesn't get charted anyway.

The total column (either a number or NA) is cell-linked into the spreadsheet that contains the charts.

Now if anyone opens this from the network drive all is fine. But most people who need to see this are remote users without that access and need this emailing. Once I email the linked cells seem to appear as REF# instead, and the charts don't show the correct numbers. (Not ideal in a dashboard, as I'm sure you'll agree).

Hope this explanation helps explain my issue. Temporarily solved by providing a PDF, but people 'liked' the Excel version, and they can play with scenarios really easily.

La. any other ideas chaps?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[xlbo]

Easiest way round all this is to "dislocate" the dashboard from the main data by running a simple macro over it to remove the links.
Once the links are removed, the dashboard can be sent out and it will be independent of the "Big" workbook

Something like:

dim c as range
For each c in worksheets("SheetName").usedrange

 if instr(c.formula,".xls")>0 then
  
    c.formula = c.value

 end if

Next

Will do if you only want to apply to external links or just simply copy/pastespecial values if you don;t have any internal links

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Welshbird]

If I then save the xls I'll have lost the links for tomorrow though won't I?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

You run the procedure to get new data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Save as a different name - Dashboard File 2011-01-11.xls

That way your daily / weekly dashboard files are all self ocntained and your "main" file remains in tact

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question