cell ranges in formulas

[Todd]

When referencing a cell range in a formula, is there a way
to represent the cell range by referencing another cell.

What I want to do is this, for example:

say cells a1 thru a100 contain a number of values.

I want to sum a certain subset of these today, but
tomorrow I want to change that subset, so I'd like to
enter the starting and ending point for my sum range into
another cell, say b1 & b2

So if I enter the text "a6" in cell b1, and "a25" in cell
b2, my sum formula should look like =sum(a6:a25). This
give me the flexibility to change my sumrange very easily
without going into the cell with the formula and actually
changing it.

 

[papou]

Hi Todd
=SUM(INDIRECT(B1 & ":" & B2)

HTH
Cordially
Pascal

 

[todd]

awesome thanks...is there a way to do this so that I can
change multiple formulas that reference the same rows but
different columns (or vice versa)

eg:
b1 = 6
b2 = 12

a1 = sum(c??:c??)
a2 = sum(d??:d??)
a3 = sum(e??:f??)

 

[papou]

Yes, you juste need to concatenate the cells addresses using INDIRECT:
=SUM(INDIRECT("C" & B1 & ":C" & B2)

HTH
Cordially
Pascal

"todd" <[email protected]> a écrit dans le message de
awesome thanks...is there a way to do this so that I can
change multiple formulas that reference the same rows but
different columns (or vice versa)

eg:
b1 = 6
b2 = 12

a1 = sum(c??:c??)
a2 = sum(d??:d??)
a3 = sum(e??:f??)

 

[todd]

Great...this is perfect...last question (hopefully)...how
can I apply this to charting the data...

ie I tried this:
=SERIES(,Sheet1!INDIRECT("A" & B1 & ":A" & B2),Sheet1!
INDIRECT("D" & B1 & "" & B2),1)

but it won't take this, I get an error...