Using a range specified in a single cell as a chart range (no VB please)

[joeyslaptop]

I need a chart that will allow me to use a formula as a range... or a reference cell's result as the range.

I have a cell with two MATCH formulas that find an item code in column B, and then provide an ordered date range from another column and displays the following:

'Data Gathered'!$O$180:$O$196

So I have a cell, H25, with the above text specified. How can I use this result as my cell range for my chart?

I have tried referring to H25 as the range, specifying =INDIRECT(H25), and pasting the formula from H25 into the chart dialog box, and at best, when it's not doing that *bonk* *error* sound, and after a little formula manipulation, I can get it to accept the formula...

... at which point I'm left with a blank chart. I know that the range I specify has data. If I use INDIRECT and just one MATCH formula, I get the first range value returned. When I use the same method and specify the last cell, I get the last value in the range. So, I know my MATCH formulas are pulling data correctly.

so to summarize...

[H25]=(fancy underlying MATCH formulas)='Data Gathered'!$O$180:$O$196

Chart wizard formula bar=(have tried all of the following)

=H25
=INDIRECT(H25)
=INDIRECT(fancy MATCH formula)
=(fancy MATCH formula used in H25)

How do I get my chart to recognize the resulting range of my fancy match formula?

 

[joeyslaptop]

Here is the fancy match formula (which works great for defining a dynamic range for VLOOKUPs), but that apparently doesn't work in charts(?):

(ADDRESS(MATCH($B6,'SQL Data'!$B:$B,0),4,1,1,"SQL Data"))&":"&(ADDRESS(MATCH($B6,'SQL Data'!$B:$B,1),4,1,1,))

where $B6 contains an item ID
SQL Data is another tab with an SQL pull
and the overall answer displayed in the cell is 'SQL Data'!$D$800:$D$818

Why can't this work in a chart?

What do I need to adjust to make it work?

Do I need this helper cell or can I paste it (perhaps with adjustment) into the chart range formula bar?

 

[joeyslaptop]

Wow... It's quiet in here.

 

[xenou]

I have been able to get this to work by creating a named range:

Named Range is named _Foo and set to:
=INDIRECT(Sheet1!$C$7)

Then in the chart data source:
=Book1.xlsx!_Foo

However, I had to edit the contents of Cell C7:
''Data Gathered'!$A$1:$A$10
That is - with an extra single quote at the front. This is because an initial single quote in a cell is a text marker and is usually ignored by Excel. Or don't use spaces in tab names. Though I'm not sure if this will apply to you since your cell has the results of a formula in it, not a raw text value. I was testing having only typed the reference into Cell C7.

I was unable to get anything to work with Indirect() directly in the chart's source data range reference - not sure if it possible.

 

[joeyslaptop]

Thanks for your response. I'll check it on Monday. Does this work in both 2003 and 2007? I'm using the Mac version: Excel 2008, and it doesn't like it. The Name box next to the adress bar stops working after I type INDIRECT. And, when I try to use the name as my range, it gives me an error.

 

[xenou]

I don't think it will work in the name bar. Open up the full name manager dialog (Ctrl + F3) for entering named formulas. I'm not a mac user but I'd expect it to work. The name manager is a bit different in Excel 2007/2010 but the basics are all the same.

As shown here (2003):
http://www.contextures.com/xlnames01.html

 

[joeyslaptop]

Oh, I did it by the name dialog box (not from the name bar). I'll just have to wait until Monday to test it at work is all. It doesn't appear to work right on the mac version I have at home.

 

[joeyslaptop]

I tried it out. Excel is automatically translating/converting the named range to its contents (the current range specified in the cell) so that when I update the named cell with a new range, the chart doesn't update.

When I go back and look at the chart data, it's filled in with the old range data (no longer makes use of the "named" property) - aka it's all hard coded into the chart range.

 

[joeyslaptop]

Thanks for your help. I found a Sparkline-charts plugin that will will allow actual named ranges (true references) rather than the built-in charts that translate named ranges into hard-coded values.

It works awesome!

The only downside is that the people viewing the data must have the plug-in activated to be able to update the charts.