Prev: Can a formula be "built" referencing text content from another cel
Next: vlookup---using data outside the range specified in the formul
From: ker_01 on 9 Mar 2010 16:48
I'm having a mental block on how to do this.

I have a boatload of named ranges in my workbook. I have one worksheet that
has two data validation cells and a graph. I want the two data validation
cells to be used to select the named range to use to populate the graph
series.

My named ranges pull from a large variety of locations (not orderly) so I
put the named range names in a table format for easier reference; here is an
example with named ranges by month and location- the user selects a month and
location, and the graph should use the named range with the name in the
corresponding cell:

Jan Feb Mar Apr etc
Loc1 Jan1 Feb1 Mar1 Apr1
Loc2 Jan2 Feb2 Mar2 Apr2
Loc3 Jan3 Feb3 Mar3 Apr3
etc

I can find the target cell no problem (Apr, Loc2 = E3) but I can't seem to
figure out how to use the cell value from my table as the named range that
feeds the graph. I tried "indirect" but either that isn't the correct
approach, or my syntax is bad (I tried it both in the graph itself, and in a
'master' named range to feed the graph).

If it were my choice, I'd just do this in VBA and have no problems, but this
workbook will be distributed internally and most folks won't enable VBA, but
will still expect the graph to dynamically update based on their two
selections.

I appreciate any help you can give me!

Thank you,
Keith
From: ker_01 on 10 Mar 2010 16:02
Some additional information, in the hopes of facilitating some expert advice:

I've done additional testing, and simple test cases work as expected. I can
create a named range for a single cell (A1), type the text name of that named
range into a second cell(A2), then reference it via =indirect(A2).

When I get into 2-cell ranges (A1:B1), and then put the named range name
"TestRange2" in B2, I note that the indirect seems to have a hidden column
reference; if I type =indirect(B2) into cell A3, it returns the value in A1;
if I type the exact same formula =indirect(B2) into cell B3, it returns the
value in B2.

I suspect that the problem in my real workbook is that my named ranges
(hundreds of them) refer to arrays of cells all over the workbook in
different columns on different sheets- so if the indirect function has some
hidden parameter, that may be what is killing me.

Since ultimately I need to feed these ranges into a graph, I tried that as
well. With my simple test case, I was able to get a very basic test working.
However, when I try to do it with any of my real ranges, it fails.

Is there some hidden parameter, and if so, what approach have others taken
to get around this problem?

Thank you,
Keith

"ker_01" wrote:

> I'm having a mental block on how to do this.
>
> I have a boatload of named ranges in my workbook. I have one worksheet that
> has two data validation cells and a graph. I want the two data validation
> cells to be used to select the named range to use to populate the graph
> series.
>
> My named ranges pull from a large variety of locations (not orderly) so I
> put the named range names in a table format for easier reference; here is an
> example with named ranges by month and location- the user selects a month and
> location, and the graph should use the named range with the name in the
> corresponding cell:
>
> Jan Feb Mar Apr etc
> Loc1 Jan1 Feb1 Mar1 Apr1
> Loc2 Jan2 Feb2 Mar2 Apr2
> Loc3 Jan3 Feb3 Mar3 Apr3
> etc
>
> I can find the target cell no problem (Apr, Loc2 = E3) but I can't seem to
> figure out how to use the cell value from my table as the named range that
> feeds the graph. I tried "indirect" but either that isn't the correct
> approach, or my syntax is bad (I tried it both in the graph itself, and in a
> 'master' named range to feed the graph).
>
> If it were my choice, I'd just do this in VBA and have no problems, but this
> workbook will be distributed internally and most folks won't enable VBA, but
> will still expect the graph to dynamically update based on their two
> selections.
>
> I appreciate any help you can give me!
>
> Thank you,
> Keith
 | 
Pages: 1
Prev: Can a formula be "built" referencing text content from another cel
Next: vlookup---using data outside the range specified in the formul