|
||||||||||||||||||||
|
|||||||||||||||||||||
|
|||||||
 |
|
|
Thread Tools | Search this Thread | Display Modes |
|
#1
05-27-2005, 06:05 PM
|
|||
|
|||
|
Can I use formulas that return cell range ref. in charts X series
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, 
|
|
#2
05-28-2005, 03:05 AM
|
|||
|
|||
|
Re: Can I use formulas that return cell range ref. in charts X series
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,
|
|
#3
05-31-2005, 08:05 PM
|
|||
|
|||
|
Re: Can I use formulas that return cell range ref. in charts X ser
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, >
|
|
#4
06-03-2005, 09:05 AM
|
|||
|
|||
|
Re: Can I use formulas that return cell range ref. in charts X ser
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, >>
|
|
#5
06-03-2005, 12:05 PM
|
|||
|
|||
|
Re: Can I use formulas that return cell range ref. in charts X ser
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, > >> >
|
|
| Bookmarks |
New topics in Excel Charting
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
