Changing ending column in chart data range - Excel 97 (long)

Changing ending column in chart data range - Excel 97 (long)

Post by Jim Englis » Thu, 03 May 2001 11:57:54



I have a workbook with about 70 charts that need to have their source data
ranges updated on a monthly basis. Needless to say, this takes several hours
to complete.

The workbook consists of three spreadsheets, two of them are where my data
ranges are stored, and the third holds my charts.

Below shows how the source data is defined for two of the charts.

Chart #1
----------------------------
Data range: =CBData!$B$57:$AX$57,CBdata!$B$61:$AX$63
Series1: =CBdata!$C$61:$AX$61
Series2: =CBdata!$C$62:$AX$62
Series3: =CBdata!$C$63:$AX$63
Category(X) Axis Labels: =CBdata!$C$57:$AX$57

Chart #2
-----------------------------
Data range: =CBdata!$B$140:$B$143,CBdata!$Q$140:$AX$143
Series1: =CBdata!$Q$141:$AX$141
Series1: =CBdata!$Q$142:$AX$142
Series1: =CBdata!$Q$143:$AX$143
Category(X) Axis Labels: =CBdata!$Q$140:$AX$140

Given this, I'd like to be able to change the ending column on all of these
cell references to the next column. In the example above, I'd like to change
"AX" to "AY" in all of the chart's cell references. This would have to
happen for all 70 charts in my "chart" spreadsheet. I tried using the
"Find...Replace" on the Edit menu, but it doesn't change chart references.

Is it possible to just put the "AY" into an empty cell and have Excel pick
this up and automatically update the cell references in all of my charts by
using some sort of function or macro? By the way, there's already data in
columns "AY" out to "AZ". I want the range to stop at whatever column I
specify.

As far as a solution goes, I don't mind making a change to the cell
reference in each chart, if it means I wouldn't have to touch it from that
point on out. I'm also open to using a macro to accomplish this. Using a
"Find...Replace" macro would be ideal, though. I'm just not familiar enough
with Excel macro programming to do this.

I've already looked at the OFFSET and INDIRECT functions, with no luck. I
also looked at the Microsoft knowledge base article:

XL: Using Defined Names to Automatically Update a Chart Range  (xl5.0 &
later)
http://support.microsoft.com/support/kb/articles/q183/4/46.asp

This article explains row expansion, but not column expansion.

I can email a sample of my workbook if desired.

Thanks,

Jim.

--------------------------------
Jim English

TB Wood's Incorporated
Chambersburg, PA 17201
http://www.tbwoods.com
--------------------------------

 
 
 

Changing ending column in chart data range - Excel 97 (long)

Post by Lars Linnebjer » Fri, 01 Jun 2001 03:00:09




> I have a workbook with about 70 charts that need to have their source data
> ranges updated on a monthly basis. Needless to say, this takes several
hours
> to complete.

> The workbook consists of three spreadsheets, two of them are where my data
> ranges are stored, and the third holds my charts.

> Below shows how the source data is defined for two of the charts.

> Chart #1
> ----------------------------
> Data range: =CBData!$B$57:$AX$57,CBdata!$B$61:$AX$63
> Series1: =CBdata!$C$61:$AX$61
> Series2: =CBdata!$C$62:$AX$62
> Series3: =CBdata!$C$63:$AX$63
> Category(X) Axis Labels: =CBdata!$C$57:$AX$57

> Chart #2
> -----------------------------
> Data range: =CBdata!$B$140:$B$143,CBdata!$Q$140:$AX$143
> Series1: =CBdata!$Q$141:$AX$141
> Series1: =CBdata!$Q$142:$AX$142
> Series1: =CBdata!$Q$143:$AX$143
> Category(X) Axis Labels: =CBdata!$Q$140:$AX$140

> Given this, I'd like to be able to change the ending column on all of
these
> cell references to the next column. In the example above, I'd like to
change
> "AX" to "AY" in all of the chart's cell references. This would have to
> happen for all 70 charts in my "chart" spreadsheet. I tried using the
> "Find...Replace" on the Edit menu, but it doesn't change chart references.

> Is it possible to just put the "AY" into an empty cell and have Excel pick
> this up and automatically update the cell references in all of my charts
by
> using some sort of function or macro? By the way, there's already data in
> columns "AY" out to "AZ". I want the range to stop at whatever column I
> specify.

> As far as a solution goes, I don't mind making a change to the cell
> reference in each chart, if it means I wouldn't have to touch it from that
> point on out. I'm also open to using a macro to accomplish this. Using a
> "Find...Replace" macro would be ideal, though. I'm just not familiar
enough
> with Excel macro programming to do this.

> I've already looked at the OFFSET and INDIRECT functions, with no luck. I
> also looked at the Microsoft knowledge base article:

> XL: Using Defined Names to Automatically Update a Chart Range  (xl5.0 &
> later)
> http://support.microsoft.com/support/kb/articles/q183/4/46.asp

> This article explains row expansion, but not column expansion.

> I can email a sample of my workbook if desired.

> Thanks,

> Jim.

> --------------------------------
> Jim English

> TB Wood's Incorporated
> Chambersburg, PA 17201
> http://www.tbwoods.com
> --------------------------------


 
 
 

1. Paste data column into every 7th column in Excel 97

Hello

May I please ask for your help.  I have an Excel document with 200
columns.  I need to insert a blank column between every 6th and 7th
column and paste a column with data into this blank column.  I would
surely do this manually but I have to do this for dozens of documents.

Perhaps you could kindly suggest a formula I can use to do this?

Thank you so much for your help

Sincerely

Tina

2. C run time for Palm!

3. Excel '97 chart changes

4. 4DOS MAME runner

5. including automatically data for one row upon clicking in a chart (Excel 97)

6. What takes my credits

7. Excel 97 - Screen column width vs. printed column width

8. Looking for TCP/IP ....

9. EXcel 97 and Importing Data from Access 97

10. How to modify Add-Ins in Excel 97 ?

11. Excel 97 - Engineering Functions add-ins

12. Excel Charting: line/column combo chart problems

13. 2D data plotting, Excel '95 vs. Excel '97