Help with variable ranges

[Nelson B.]

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.

 

[Barb Reinhardt]

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/Pub0009/LPMArticle.asp?ID=246

 

[Don Guillett]

Pls post in ONE group only.

 

[JMay]

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,

 

[JMay]

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 !!

 

[Nelson B.]

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

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 !!