• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

dan_l

Active Member
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

Excel Rōnin
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

Active Member
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?
 

dan_l

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


aha!
 

SirJB7

Excel Rōnin
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:

-----

Code:
Option Explicit

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


I think it's my best shot.


Regards!
 

Ceriel

New Member
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

New Member
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:

-----

Code:
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.
 
Top