Can I use formulas that return cell range ref. in charts X series

[cwilliams]

I'm processing data in a very large data file and have multiple data files to
process. To expidite the process I want to use VLOOKUP to lookup a time stamp
and return cooresponding row indices. I've accomplished this suing the
formuals below:

=VLOOKUP(H8,Data!$B$5:$T$64000,19); returns 455 from an index row in the
imported data file.

Now I want to automatically update the X & Y range series in multiple
charts. I tried to do this with the formula below that works in a worksheet
cell but it gives me en error when I paste the formula in the X or Y series
box of the chart.

=("Data!B"&INDEX(J8:K13,1,1)&":B"&INDEX(J8:K13,1,2)); returns Data!B455:B1355
where 455 is located in cell J8

Can anyone suggest a way to accomplish what I'm trying to do?

Thanks,

[Jon Peltier]

You can't put these formulas in the dialog entries for a chart, but you
can use the formulas to define dynamic ranges, and use the ranges in the
chart series dialog. The first few examples on this page link to more
detailed explanations.

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


cwilliams wrote:

> I'm processing data in a very large data file and have multiple data files to
> process. To expidite the process I want to use VLOOKUP to lookup a time stamp
> and return cooresponding row indices. I've accomplished this suing the
> formuals below:
>
> =VLOOKUP(H8,Data!$B$5:$T$64000,19); returns 455 from an index row in the
> imported data file.
>
> Now I want to automatically update the X & Y range series in multiple
> charts. I tried to do this with the formula below that works in a worksheet
> cell but it gives me en error when I paste the formula in the X or Y series
> box of the chart.
>
> =("Data!B"&INDEX(J8:K13,1,1)&":B"&INDEX(J8:K13,1,2)); returns Data!B455:B1355
> where 455 is located in cell J8
>
> Can anyone suggest a way to accomplish what I'm trying to do?
>
> Thanks,

[cwilliams]

Thanks for the reply! In my application I'm trying to enter a name: T1_Time
that referrs to: ="Data!$B$"&'Run Sheet'!$J$8&":$B$"&'Run Sheet'!$K$8.

When I enter the above in a worksheet cell it displays: Data!$B$703:$B$1603.

When I type the name in the series box I get a reference error? ANy
suggestions?

FYI. the values in cells referenced by "Run Sheet!J8 & "Run Sheet!K8 will
change and hence so will my graph.


"Jon Peltier" wrote:

> You can't put these formulas in the dialog entries for a chart, but you
> can use the formulas to define dynamic ranges, and use the ranges in the
> chart series dialog. The first few examples on this page link to more
> detailed explanations.
>
> http://peltiertech.com/Excel/Charts/Dynamics.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> cwilliams wrote:
>
> > I'm processing data in a very large data file and have multiple data files to
> > process. To expidite the process I want to use VLOOKUP to lookup a time stamp
> > and return cooresponding row indices. I've accomplished this suing the
> > formuals below:
> >
> > =VLOOKUP(H8,Data!$B$5:$T$64000,19); returns 455 from an index row in the
> > imported data file.
> >
> > Now I want to automatically update the X & Y range series in multiple
> > charts. I tried to do this with the formula below that works in a worksheet
> > cell but it gives me en error when I paste the formula in the X or Y series
> > box of the chart.
> >
> > =("Data!B"&INDEX(J8:K13,1,1)&":B"&INDEX(J8:K13,1,2)); returns Data!B455:B1355
> > where 455 is located in cell J8
> >
> > Can anyone suggest a way to accomplish what I'm trying to do?
> >
> > Thanks,
>

[Jon Peltier]

Let me restate:

>>You can't put these formulas in the dialog entries for a chart

What you need to do, as spelled out in the link I provided, is create a
name in the worksheet. Insert menu > Names > Define to open the dialog.
Type an appropriate name in the box labeled Name, and in the box labeled
Refers To, enter your formula, modified slightly to turn the string
address into a range reference:

=INDIRECT("Data!$B$"&'Run Sheet'!$J$8&":$B$"&'Run Sheet'!$K$8)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


cwilliams wrote:

> Thanks for the reply! In my application I'm trying to enter a name: T1_Time
> that referrs to: ="Data!$B$"&'Run Sheet'!$J$8&":$B$"&'Run Sheet'!$K$8.
>
> When I enter the above in a worksheet cell it displays: Data!$B$703:$B$1603.
>
> When I type the name in the series box I get a reference error? ANy
> suggestions?
>
> FYI. the values in cells referenced by "Run Sheet!J8 & "Run Sheet!K8 will
> change and hence so will my graph.
>
>
> "Jon Peltier" wrote:
>
>
>>You can't put these formulas in the dialog entries for a chart, but you
>>can use the formulas to define dynamic ranges, and use the ranges in the
>>chart series dialog. The first few examples on this page link to more
>>detailed explanations.
>>
>> http://peltiertech.com/Excel/Charts/Dynamics.html
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>cwilliams wrote:
>>
>>
>>>I'm processing data in a very large data file and have multiple data files to
>>>process. To expidite the process I want to use VLOOKUP to lookup a time stamp
>>>and return cooresponding row indices. I've accomplished this suing the
>>>formuals below:
>>>
>>>=VLOOKUP(H8,Data!$B$5:$T$64000,19); returns 455 from an index row in the
>>>imported data file.
>>>
>>>Now I want to automatically update the X & Y range series in multiple
>>>charts. I tried to do this with the formula below that works in a worksheet
>>>cell but it gives me en error when I paste the formula in the X or Y series
>>>box of the chart.
>>>
>>>=("Data!B"&INDEX(J8:K13,1,1)&":B"&INDEX(J8:K13,1,2)); returns Data!B455:B1355
>>>where 455 is located in cell J8
>>>
>>>Can anyone suggest a way to accomplish what I'm trying to do?
>>>
>>>Thanks,
>>

[cwilliams]

Thanks a lot Jon!
For the record I did read the link you sent and I entered my formula into
the box labeled Refers To. But I was trying to do so without using the
Indirect function.

Thanks again.

"Jon Peltier" wrote:

> Let me restate:
>
> >>You can't put these formulas in the dialog entries for a chart
>
> What you need to do, as spelled out in the link I provided, is create a
> name in the worksheet. Insert menu > Names > Define to open the dialog.
> Type an appropriate name in the box labeled Name, and in the box labeled
> Refers To, enter your formula, modified slightly to turn the string
> address into a range reference:
>
> =INDIRECT("Data!$B$"&'Run Sheet'!$J$8&":$B$"&'Run Sheet'!$K$8)
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> cwilliams wrote:
>
> > Thanks for the reply! In my application I'm trying to enter a name: T1_Time
> > that referrs to: ="Data!$B$"&'Run Sheet'!$J$8&":$B$"&'Run Sheet'!$K$8.
> >
> > When I enter the above in a worksheet cell it displays: Data!$B$703:$B$1603.
> >
> > When I type the name in the series box I get a reference error? ANy
> > suggestions?
> >
> > FYI. the values in cells referenced by "Run Sheet!J8 & "Run Sheet!K8 will
> > change and hence so will my graph.
> >
> >
> > "Jon Peltier" wrote:
> >
> >
> >>You can't put these formulas in the dialog entries for a chart, but you
> >>can use the formulas to define dynamic ranges, and use the ranges in the
> >>chart series dialog. The first few examples on this page link to more
> >>detailed explanations.
> >>
> >> http://peltiertech.com/Excel/Charts/Dynamics.html
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>
> >>cwilliams wrote:
> >>
> >>
> >>>I'm processing data in a very large data file and have multiple data files to
> >>>process. To expidite the process I want to use VLOOKUP to lookup a time stamp
> >>>and return cooresponding row indices. I've accomplished this suing the
> >>>formuals below:
> >>>
> >>>=VLOOKUP(H8,Data!$B$5:$T$64000,19); returns 455 from an index row in the
> >>>imported data file.
> >>>
> >>>Now I want to automatically update the X & Y range series in multiple
> >>>charts. I tried to do this with the formula below that works in a worksheet
> >>>cell but it gives me en error when I paste the formula in the X or Y series
> >>>box of the chart.
> >>>
> >>>=("Data!B"&INDEX(J8:K13,1,1)&":B"&INDEX(J8:K13,1,2)); returns Data!B455:B1355
> >>>where 455 is located in cell J8
> >>>
> >>>Can anyone suggest a way to accomplish what I'm trying to do?
> >>>
> >>>Thanks,
> >>
>