Passing a named range to a formula with indirect....

[dan_l]

I forget how I did this before.


I've got a couple of dynamic named ranges. They're interchangeable for a particular dynamic chart. Cell A2 contains text that matches a named range.


I want to =sum(indirect(a2)) but it keeps returning #ref. I looked up the last time I did something like this and it was buried in a vlookup - but it worked.


"=VLOOKUP($A30,INDIRECT(VLOOKUP(s_mode,l_cc_nrlookup,2,FALSE)),VLOOKUP(B$29,lst_month_r,2,FALSE),FALSE)"


If I had to hazard a guess, I'm thinking it needs a text delimiter or something like sum(indirect(concatenate(""",a2,""")) or something.


Any guesses?

 

[SirJB7]

Hi, dan_l!


Tried debugging formula with F9? Or using 2 temporary helper columns to see what retrieves these parts?

VLOOKUP(s_mode,l_cc_nrlookup,2,FALSE)

VLOOKUP(B$29,lst_month_r,2,FALSE)


Regards!

 

[dan_l]

Actually: this is weird.


I just tested it with a fixed named range and it works fine. Maybe the moral of the story is that you can't use indirect with a dynamic named range?

 

[SirJB7]

Hi, dan_l!

I'm just leaving, but I'll try it tomorrow and post here.

Regards!

 

[dan_l]

http://www.dailydoseofexcel.com/archives/2005/03/01/indirect-and-dynamic-ranges/


aha!

 

[SirJB7]

Hi, dan_l!

So it was that... I didn't know it.

Regards!

 

[SirJB7]

Hi, dan_l!


Friday night, back from friends meeting, 3AM, something pending before going to sleep, and here it is... I found a little trick.


Give a look at this file:

https://dl.dropbox.com/u/60558749/P... indirect.... (for dan_l at chandoo.org).xlsm


In E1, indirect with static range, 10, Ok.

In E2, indirect with dynamic range, error.

In E3, indirect with address of dynamic range, 10, Ok.


How? A little UDF:

-----

Option Explicit

Function RetrieveRangeForName(psRange As String) As String
RetrieveRangeForName = Range(psRange).Address
End Function

-----


I think it's my best shot.


Regards!

 

[Ceriel]

Hi, I found this site on my quest for an asnwer to my problem.
I am a excel noob and I am having the next problem:

I want a depended drop down list.
Like in A1 there is a drop down with an defined name "tbl_countries"
Containing USA and CANADA
Then there are 2 defined name ranges: USA and CANADA containing:
USA: new york / Atlanta
CANADA: Vancouver / Toronto

Now I want tho make a second drop down with the INDIRECT function. But cant do this with dynamic ranges.

So can I implement this VBA code to make it work and if so how?

 

[Olugbenga]

Hi, dan_l!


Friday night, back from friends meeting, 3AM, something pending before going to sleep, and here it is... I found a little trick.


Give a look at this file:

https://dl.dropbox.com/u/60558749/Passing a named range to a formula with indirect.... (for dan_l at chandoo.org).xlsm


In E1, indirect with static range, 10, Ok.

In E2, indirect with dynamic range, error.

In E3, indirect with address of dynamic range, 10, Ok.


How? A little UDF:

-----

Option Explicit

Function RetrieveRangeForName(psRange As String) As String
RetrieveRangeForName = Range(psRange).Address
End Function

-----


I think it's my best shot.


Regards!

This approach actually did the trick. Although I had to concatenate the output with sheet name to work perfectly.