PDA

View Full Version : Solved: Use cell value in formula as Dynamic Range



JimS
01-19-2012, 07:38 PM
This formula works if the Range Name, which happens to be the contents of cell C1, is a Static Range Name.

=SUM(INDIRECT(C1))

This will not work if the Range Name, which is the contents of cell C1 is a Dynanic Range Name.

Any ideas?

Thanks...

JimS

Trebor76
01-20-2012, 12:37 AM
If the named range is set to dynamically include additional rows, the INDIRECT function will work (well it just did for me in Excel 2007 anyways).

You could just sum the named range directly i.e. if the named range is called MyNamedRange, simply use the following:

=SUM(MyNamedRange)

HTH

Robert

JimS
01-20-2012, 08:27 AM
Adding an additional row doesn't work for me.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)+1,1)

Using the Range Name in the formula won't help me either.

Neither will this method: =SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)))

Thanks for trying though...

JimS

Trebor76
01-21-2012, 04:23 AM
Strange, besides the "+1" the offset formula seems correct, i.e I would have thought it should be this:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

If you could attach a sample workbook it may shed some light on what's happening.

JimS
01-23-2012, 08:01 AM
I've attached an example. The reason for having the +1 in the Offset Range Name was to add an additional row per your first reply.

I have 2 different Dynamic Ranges defined now and neither one works, 1 with an extra row and 1 without.

JimS

Trebor76
01-23-2012, 02:59 PM
This is a limitation of the INDIRECT function in that while the function can convert a string into a range, it cannot evaluate formulas that use worksheet functions. This is why, from your posted workbook, referencing rang1 with INDIRECT works, but not rang2 or rang3.

There are two solutions I can think of:

1. Just sum the named ranges directly (re my first post), i.e. = SUM(rang1), =SUM(rang2), =SUM(rang3), or
2. Use the following User Defined Function:

Option Explicit
'Adapted from: http://www.quantnet.com/forum/threads/indirect-dynamic-named-range-in-excel-2007.1800/
Function udfINDIRECT(strRange As String) As Range
'Usage: in a cell use the following formula... _
=SUM(udfINDIRECT(C2))
'...to sum the named range housed in cell C2

Set udfINDIRECT = Range(strRange)

End Function

HTH

Robert

JimS
01-24-2012, 08:05 AM
Robert,

Thank you for your help.

This just what I needed.

Also, thank you for the explaination of why the Indirect will not work in this case.
The explaination helps understand better of how excel works (and sometimes does not work).

Thanks again...

JimS

Trebor76
01-24-2012, 03:29 PM
You're welcome. I'm glad VBAX was able to provide a workable solution for you :)