Making ranges adjust automatically

[Nelson B.]

I desperately need some help with varying a range in a function. I'm trying
to make some of my reports more automated, and I would like to be able to
fill out one reference cell with the current month and have the formulas now
include any new data into their calculations. I can't simply use yearly
totals as I often want to compare the same time period last year to the
current YTD.

For example, if January's data is in cell C4 and I want to add the current
YTD I'd like to be able to write a formula that is SUM(C4:C("current month")).

 

[Mike H]

You've posted this question several times and have several answers that you
haven't responded to. Whats' wrong with those answers?

 

[Bernie Deitrick]

Nelson,

For example, if January's data is in cell C4 and I want to add the current
YTD I'd like to be able to write a formula that is SUM(C4:C("current month")).

=SUM(OFFSET(C4,0,0,MONTH(TODAY()),1))

HTH,
Bernie
MS Excel MVP

 

[Daniel Gash-Jones]

Use the indirect formula so for example if you have a different month on each sheet called Jan, Feb etc you would have a formula like =INDIRECT("C4:C(" & myref) where myref would be a cell which changes in value according to what you choose in your drop down. Personally I would have this cell in a hidden column, it would vlookup the dropdown box value in a table to return the row number for the selected month. I'd also usually hide this table in either hidden columns or on a hidden sheet in the backgound.

HTH

 

[Nelson B.]

I haven't been able to get those answers to work. The answer from JMay works
for me in some situations, but I'm unclear on the variables in the address
formula, and can't seem to adjust them to suit my needs. His answer is below:

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

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,

I did actually reply to his post and did not receive an answer. I've since
figured out the replied question, but still cannot get the solution to work
for me consistently. So I thought I'd re-post and try again. I had no idea
it would upset you so greatly.