dynamic chart of formulas

[kendel]

Hello, I'm using WinXP with Excel 2007.
I would like to chart a range of summarized data that includes future dates. The range is B3:U110. All the cells in the range have formulas that link to where the data is entered:
'C:\ProdGuys\a PRODUCTION DATA\Set up Times\2012\[01 jan print stats.xls]week 3'!B$3
The summary sheet covers a years worth of data, which is collected daily, and summarized by the week. The cell formulas linked to future data return a "" untill the linked data is entered.

This is the dynamic named range, with an option to specify how many weeks, last 13, etc..., to chart (data valedation in A1):

OFFSET(scott!$O$3,COUNTA(scott!$O$3:$O$110)-scott!$A$1,,scott!$A$1)

Cells returning the "" of future data, chart as a gap. So to see the first weeks of the year A1 must equal 52 which leaves alot of gaps in the chart, and making a trendline worthless. My work around is to move those future weeks out of the range. I don't like that because somebody has to do something to maintain the charts. I want the charts to stand alone with no muss and fuss with just the simple daily data entry.
Can this formula be revised to ignore cells with formulas that return a ""? Or please suggest a different strategy.

Thanks,
Kendel

 

[Robert Mika]

Yes it can try this:
http://www.mrexcel.com/forum/showthread.php?p=2941123#post2941123

 

[kendel]

Hi Robert,
Thanks for your response, unfortunately this doesn't work to eliminate the gaps in the chart and I lose the 'last N weeks' function.
I appreciate your taking the time to respond, perhaps you could suggest another way?
It seems the difficulty lies in the fact that all cells in the range have either a formula or a link to cells that contain a formula in them, and the chart function sees all the cells as containing something. I'm trying to trick the chart into ignoring those cells that are not blank but have no data.

Thanks again for you help!

 

[kendel]

Please help,

Thanks

 

[kendel]

anybody have a hint or a 'try this it might work'?
thanks

 

[NARAYANK991]

Hi ,

Can you post a sample worksheet ? Trying to visualize your worksheet based on the details you have given is probably difficult.

Normally , if the chart data range is dynamic , and the X-axis is dynamic , then you should not have the blank gaps ; I assume that dynamic means the range extends only as far as the data is available , so that blank cells will not form part of the range.

Narayan

 

[kendel]

Hi Narayan, thank you for your response.

This range I want to chart is not a range of cells that contain data values.

Col A is the 52 weeks of the year.
Col B thru K are links to metric data on another worksheet. Col L thru U are formulas using the data returned by those links.

So in that sense there are no 'blank' cells but many cells with no data, just links and formulas, that chart as gaps.

I am seeking to define the range of each column in such a way as to ignore any cells that contain links or formulas that return a "".

I don't yet know how to post a sheet to this site, so I hope this will help.

Thanks again for any help or advice,
Kendel

 

[NARAYANK991]

Hi ,

I think this is a problem for which most of the earlier posters have not been able to find a solution ; the following link is an earlier one , whose replies you can check out :

http://www.mrexcel.com/forum/showthread.php?t=53675

Hiding those rows which have blanks in them , prevents the data points from being plotted. Is this an option for you ?

Narayan

 

[kendel]

Hi Narayan, thanks again for posting.
Thanks for the links, they were informative but my cells are not blank, they're waiting for future data.
Hiding the rows is what I'm doing. There are 22 spreadsheets that I'm charting, so as I'm sure you can understand, hiding/unhiding again and again for all the weeks of a year is pretty tedious
I'm thinking I may have to revise the charting of the data from the weekly summarized data to the daily data; darn that means I gotta actually learn how to build pivot tables Alls good.

Thanks again and if you learn or figure out how to trick a chart into ignoring a link or formula in it's range...

Kendel