Using Formulas for Charts

[business models]

Using Excel 2007, I need to update numerous charts on a monthly basis. All data is contained within 1 workbook with numerous worksheets. The charts present information from multiple worksheets. I am searching for a way to use a formula that defines the number of data points being charted. ALL worksheets follow a similar format:
Variable name is in A1 (i.e., revenue); monthly data begins in col E and continues.

I created a formula that allows me to define the range desired using defined names. How do I incorporate this formula in a chart? The formula is valid within the "SUM" function; but i need to utilize it within a chart.

[TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl389, width: 72"]=SUM(INDIRECT(ADDRESS(117,Begin_Mo_History,,TRUE,"ISHistory")&":"&ADDRESS(117,End_Mo_History,,TRUE, ) ) )

Defined names: Begin_Mo_History = 5 (col E); End_Mo_History = 46 (col AT)

My goal is to reproduce the following range ('IS History'!$E$117:$AT$117) in a chart using the formula above. Can this be done?[/TD]
[/TR]
</tbody>[/TABLE]

 

[Robert Mika]

Woudl this help:
http://www.mrexcel.com/forum/excel-...-columns-data-based-formulas.html#post2941123

 

[tusharm]

You don't need to specify the beginning and ending cells through defined names. Instead, you can have Excel compute the number of cells to plot. Also, no need for INDIRECT(ADDRESS(...))

Use OFFSET or INDEX.

So, if you have no data in row 117 other than what you want to plot in E117:{whatever}117, create a named formula
OFFSET(e117,0,0,1,COUNTA(117:117)). Use this name in a chart.

For details see Dynamic Charts

There are links to other pages that explain how to create a named formula and how to use a named formula in a chart.

The examples all work with data in rows rather than tables but those references are easy to adjust.

Using Excel 2007, I need to update numerous charts on a monthly basis. All data is contained within 1 workbook with numerous worksheets. The charts present information from multiple worksheets. I am searching for a way to use a formula that defines the number of data points being charted. ALL worksheets follow a similar format:
Variable name is in A1 (i.e., revenue); monthly data begins in col E and continues.

I created a formula that allows me to define the range desired using defined names. How do I incorporate this formula in a chart? The formula is valid within the "SUM" function; but i need to utilize it within a chart.

[TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl389, width: 72"]=SUM(INDIRECT(ADDRESS(117,Begin_Mo_History,,TRUE,"ISHistory")&":"&ADDRESS(117,End_Mo_History,,TRUE, ) ) )

Defined names: Begin_Mo_History = 5 (col E); End_Mo_History = 46 (col AT)

My goal is to reproduce the following range ('IS History'!$E$117:$AT$117) in a chart using the formula above. Can this be done?[/TD]
[/TR]
</tbody>[/TABLE]

 

[business models]

I have tried to use the INDEX function. I created the following function in Excel 2007, but an error message appeared when trying to add it as the Series Value in the Select Data Source box.
='IS History'!$E$117:INDEX('IS History'!$117:$117,COUNT('IS History'!$117:$117))
With values to chart in columns E through AZ of row 117, what should the formula be? My intent is to dynamically update the chart as new data is added.

 

[tusharm]

I can guess what the problem is but here's how you can learn for yourself.

A good way to learn what is wrong with a named formula is to enter it in a worksheet (not the name but the actual formula). Then, use the Formula Auditor to explore the problem.

So, with data in say E117:G117...
In A118:C118 array enter your formula. You should get an error. Now, select A118 and use the Formulas tab | Formula Auditing group | Evaluate Formula button.

I have tried to use the INDEX function. I created the following function in Excel 2007, but an error message appeared when trying to add it as the Series Value in the Select Data Source box.
='IS History'!$E$117:INDEX('IS History'!$117:$117,COUNT('IS History'!$117:$117))
With values to chart in columns E through AZ of row 117, what should the formula be? My intent is to dynamically update the chart as new data is added.

 

[business models]

As requested, the formula was keyed into A118. I made a slight adjustment to the formula to include 4 additional columns:
='IS History'!$E$117:INDEX('IS History'!117:117,(COUNT('IS History'!117:117)+4))


Using Evaluate Formula button, the following resulted:
Reference Evaluation
'IS History'!$A$118 $E$117:$AT$117

the final evaluation produced: #VALUE!

Can you tell from the above what I am missing to complete the formula for the charted variable?