Moving Links one coloumn to the right each Month

[Andrew Davroche]

Excel 2003
I have a "Data" worksheet with columns of "months" and rows of indicators
like "Sales" and "Margin" etc. I have a "Summary" worksheet linked to the
"Data" worksheet via many formulas. Each new month I want the formulas in
the summary sheet to move to the new month column in the data worksheet. How
can I do this?

I also use ranges like "Todate" so I want to extend the range by one column
each month but keep the first (or anchor) month.

 

[Bob Greenblatt]

Excel 2003
I have a "Data" worksheet with columns of "months" and rows of indicators
like "Sales" and "Margin" etc. I have a "Summary" worksheet linked to the
"Data" worksheet via many formulas. Each new month I want the formulas in
the summary sheet to move to the new month column in the data worksheet. How
can I do this?

I also use ranges like "Todate" so I want to extend the range by one column
each month but keep the first (or anchor) month.

There are a whole bunch of ways to do this. Look in help for OFFSET, and use
it in the formulas to get data from the column you want. Just enter a month
number somewhere to represent the month "offset." You can also use self
defining names to make an adjustable size range. For example, you can define
a name as YearToDate to refer to a varying number of months. The refers to
field will look something like:
=offset($a$1,0,0,5,counta($1:$1))

This will define an area 5 rows deep, and a varying number of columns
depending on how many month names are used in row 1.

 

[Andrew Davroche]

Thank you so much - I can change all my formulas simply by changing the data
in one cell - brilliant. I wish I'd asked years ago.

 

[Andrew Davroche]

With your help I have been extensively using Offset. I have a set of sixteen
formulas which use the same reference cell. When I want to use the same set
of sixteen formulas with another reference cell I have to edit each offset
formula with the new reference cell. Is there a quicker way of doing this.
I have used a name "Fred" instead of say "B56" but I still need to change
"Fred" sixteen times.

I have read help but I am not getting it.

 

[Bob Greenblatt]

With your help I have been extensively using Offset. I have a set of sixteen
formulas which use the same reference cell. When I want to use the same set
of sixteen formulas with another reference cell I have to edit each offset
formula with the new reference cell. Is there a quicker way of doing this.
I have used a name "Fred" instead of say "B56" but I still need to change
"Fred" sixteen times.

I have read help but I am not getting it.

Have you tried edit-Replace?