Dynamic ranges

[Lolo13]

Hi, i have created multiple dynamic ranges using the offset function and these ranges are working fine; for instance, range1, range , range 3, etc... All these ranges contain numbers.
If i do a sum(range1) i do get the proper sum of values contained in range1. However, instead of calling directly the range in the sum function as previously mentioned i need to call it from a cell, see below for clarity:

So the first two lines are working fine but somehow i cannot refer to a working range via a cell content... Is there a workaround ?
Thanks.

 

[Eric W]

Welcome to the MrExcel forum!

It works for me, what are you getting as a result?

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	range1	10		1	2	2
2	range2	17		2	3	4
3	range3	20		3	5	6
4				4	7	8
Sheet6

Cell Formulas
Range	Formula
B1	B1	=SUM(Range1)
B2	B2	=SUM(Range2)
B3	B3	=SUM(INDIRECT(A3))

Named Ranges
Name	Refers To	Cells
Range1	=Sheet6!$D$1:$D$4	B1
Range2	=Sheet6!$E$1:$E$4	B2

 

[Lolo13]

it works because you are not using dynamic ranges. Here is what i want to do using your example:

and actual results:

 

[Lolo13]

Sorry on my previous post, the results look like this:

 

[Eric W]

Sorry, I missed that part. I replicated your issue on my sheet. I've poked around a bit, and can't explain it. =SUM(Range1) works, but ROWS(Range1) doesn't. I've tried rewriting your dynamic formulas using INDEX instead of OFFSET, but that didn't help either. I'll see what else I can come up with.

 

[Lolo13]

Thanks, i have been banging my head for hours on this ... It seems like such a "trivial" issue....

 

[Eric W]

I've tried a few more things, and come up empty. I did a web search, and found people asking this question for over 10 years, and no one's had an answer. Just something Microsoft has never gotten around to fixing. One poster mentioned that you can use INDIRECT with tables, although you might not be able to change your sheet to make that work. You might be able to get away with using static ranges. For example, if Range2 is defined as: =OFFSET(Sheet6!$E$1,0,0,COUNT(Sheet6!$E$1:$E$10)), the COUNT function counts the number of numeric values. If you change Range2 to be =$E$1:$E$10, and use =SUM(INDIRECT(A2)), it works. SUM only adds up numeric values anyway, so keeping the whole range won't affect the result. But that depends on what you use your dynamic named ranges for.

Again, depending on your layout, something like this:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	Range1	10		Range1	Range2	Range3
2	Range2	17		1	2	2
3	Range3	30		2	3	4
4				3	5	6
5				4	7	8
6						10
7
Sheet6

Cell Formulas
Range	Formula
B1:B3	B1	=SUM(INDEX($D$2:$F$10,0,MATCH(A1,$D$1:$F$1,0)))

Named Ranges
Name	Refers To	Cells
Range1	=OFFSET(Sheet6!$D$2,0,0,COUNTA(Sheet6!$D$2:$D$11))	B1:B3
Range2	=OFFSET(Sheet6!$E$2,0,0,COUNT(Sheet6!$E$2:$E$11))	B1:B3
Range3	=Sheet6!$F$2:INDEX(Sheet6!$F$2:$F$11,COUNT(Sheet6!$F$2:$F$11))	B1:B3

So there's probably a way to do what you want, but you may need to change things around some, and it won't involve INDIRECT with DNRs.

 

[Lolo13]

Thanks a bunch for looking inti this Eric! Unfortunately though, really need dynamic ranges for my application. Moreover, I was just using SUM as an example;in reality i am using a custom function that is even more complicated....
In any case, thanks for your time!

 

[Eric W]

Is it a custom function built from spreadsheet functions, or a UDF built from VBA? If from VBA, you can use something like:

range(range("A3")).Address

to get the address of the dynamic range, and insert that into later functions.

Anyway, sorry I wasn't more help, but good luck!