Help with variable ranges
 

I need some help making some of my reports more automated. What I'm trying
to accomplish is being able to fill in a reference cell for the current
month, and have ranges in varying functions adjust to include the new month.
This way I can enter the new data and not worry about having to go and adjust
every formula to include it. For example, if January's data is in cell C4
and I want to sum the data for January thru August I'd like to be able to
write the formula to SUM(C4:C("current month")). I've tried doing this
various ways, but have been unsuccessful.

Thanks for any suggestions.

Help with variable ranges
 

This article from Jon Peltier should help get you started. It applies to
dynamic charting, but the concept is the same.

http://pubs.logicalexpressions.com/P...cle.asp?ID=246
--
HTH,
Barb Reinhardt



"Nelson B." wrote:

I need some help making some of my reports more automated. What I'm trying
to accomplish is being able to fill in a reference cell for the current
month, and have ranges in varying functions adjust to include the new month.
This way I can enter the new data and not worry about having to go and adjust
every formula to include it. For example, if January's data is in cell C4
and I want to sum the data for January thru August I'd like to be able to
write the formula to SUM(C4:C("current month")). I've tried doing this
various ways, but have been unsuccessful.

Thanks for any suggestions.

Help with variable ranges
 

Pls post in ONE group only.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nelson B." <Nelson wrote in message
...
I need some help making some of my reports more automated. What I'm trying
to accomplish is being able to fill in a reference cell for the current
month, and have ranges in varying functions adjust to include the new
month.
This way I can enter the new data and not worry about having to go and
adjust
every formula to include it. For example, if January's data is in cell C4
and I want to sum the data for January thru August I'd like to be able to
write the formula to SUM(C4:C("current month")). I've tried doing this
various ways, but have been unsuccessful.

Thanks for any suggestions.

Help with variable ranges
 

Sample:
A1 = 3 << Your Variable (months or columns to stretch out to)

B3 = 123
C3 = 456
D3 = 789

formula in cell A3

=SUM(B3:INDIRECT(ADDRESS(3,A1+1,4)))

HTH,




"Nelson B." wrote:

I need some help making some of my reports more automated. What I'm trying
to accomplish is being able to fill in a reference cell for the current
month, and have ranges in varying functions adjust to include the new month.
This way I can enter the new data and not worry about having to go and adjust
every formula to include it. For example, if January's data is in cell C4
and I want to sum the data for January thru August I'd like to be able to
write the formula to SUM(C4:C("current month")). I've tried doing this
various ways, but have been unsuccessful.

Thanks for any suggestions.

Help with variable ranges
 

Better alter cell A1 below to $A$1

=SUM(B3:INDIRECT(ADDRESS(3,A1+1,4)))
to
=SUM(B3:INDIRECT(ADDRESS(3,$A$1+1,4)))

so when you copy the formula down it works !!


"JMay" wrote:

Sample:
A1 = 3 << Your Variable (months or columns to stretch out to)

B3 = 123
C3 = 456
D3 = 789

formula in cell A3

=SUM(B3:INDIRECT(ADDRESS(3,A1+1,4)))

HTH,




"Nelson B." wrote:

I need some help making some of my reports more automated. What I'm trying
to accomplish is being able to fill in a reference cell for the current
month, and have ranges in varying functions adjust to include the new month.
This way I can enter the new data and not worry about having to go and adjust
every formula to include it. For example, if January's data is in cell C4
and I want to sum the data for January thru August I'd like to be able to
write the formula to SUM(C4:C("current month")). I've tried doing this
various ways, but have been unsuccessful.

Thanks for any suggestions.

Help with variable ranges
 

How do I do this for vertical data in the range?

"JMay" wrote:

Better alter cell A1 below to $A$1

=SUM(B3:INDIRECT(ADDRESS(3,A1+1,4)))
to
=SUM(B3:INDIRECT(ADDRESS(3,$A$1+1,4)))

so when you copy the formula down it works !!


"JMay" wrote:

Sample:
A1 = 3 << Your Variable (months or columns to stretch out to)

B3 = 123
C3 = 456
D3 = 789

formula in cell A3

=SUM(B3:INDIRECT(ADDRESS(3,A1+1,4)))

HTH,




"Nelson B." wrote:

I need some help making some of my reports more automated. What I'm trying
to accomplish is being able to fill in a reference cell for the current
month, and have ranges in varying functions adjust to include the new month.
This way I can enter the new data and not worry about having to go and adjust
every formula to include it. For example, if January's data is in cell C4
and I want to sum the data for January thru August I'd like to be able to
write the formula to SUM(C4:C("current month")). I've tried doing this
various ways, but have been unsuccessful.

Thanks for any suggestions.