multiple cell references

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Branko - 03 Apr 2005 21:25 GMT

If I have in a cell reference
=SUM(A3:A300)
how to have 3 and 300 referenced to another cell so that I can change range
without changing formula?

Reply to this Message

David McRitchie - 03 Apr 2005 22:37 GMT

You could use a defined name for the range, or if you really must
you could use INDIRECT
=SUM(INDIRECT($B$3))

but possibly you are trying to compensate for a formula that
might better be written as
=SUM(A$3, OFFSET(A301,-1,0))
if you are concerned about inserting or deleting rows
above row 301 and below row 3.

http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> If I have in a cell reference
> =SUM(A3:A300)
> how to have 3 and 300 referenced to another cell so that I can change range
> without changing formula?

Reply to this Message

Branko - 03 Apr 2005 23:39 GMT

What I want is to have 3 and 300 (in my example) in another cell as variable
so by changing those inputs I would change the desired range.

> You could use a defined name for the range, or if you really must
> you could use INDIRECT
[quoted text clipped - 17 lines]
> > how to have 3 and 300 referenced to another cell so that I can change range
> > without changing formula?

Reply to this Message

David McRitchie - 04 Apr 2005 01:12 GMT

Sorry I had not tested INDIRECT

=SUM(INDIRECT(L18 & ":" & M18)) where L18: A3 and M18: A300

=SUM(INDIRECT(TEXT(L17,"@"))) where L17: A3:A300

must be a shorter solution using INDIRECT

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> What I want is to have 3 and 300 (in my example) in another cell as variable
> so by changing those inputs I would change the desired range.
[quoted text clipped - 20 lines]
> > > how to have 3 and 300 referenced to another cell so that I can change range
> > > without changing formula?

Reply to this Message

Branko - 04 Apr 2005 02:57 GMT

Thank you David, it works.

> Sorry I had not tested INDIRECT
>
[quoted text clipped - 34 lines]
> > > > how to have 3 and 300 referenced to another cell so that I can change range
> > > > without changing formula?

Reply to this Message

David McRitchie - 04 Apr 2005 04:50 GMT

You're welcome.

This is a bit better the A1 is to indicate type of reference A1 or R1C1
syntax: INDIRECT(ref_text,a1)

=SUM(INDIRECT(L1, True) where L1: 'A3:A300

since True is the default and is for A1 style reference, I don't know
why this originally failed when I tested, perhaps I had a leading
space in the L1 value
=SUM(INDIRECT(L1))

I will update my indirect.htm web page accordingly
http://www.mvps.org/dmcritchie/excel/indirect.htm
and test my replies a bit more often as well.
---
David

> Thank you David, it works.
>
[quoted text clipped - 36 lines]
> > > > > how to have 3 and 300 referenced to another cell so that I can change range
> > > > > without changing formula?

Reply to this Message