Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
up to 7 functions? | Excel Worksheet Functions | |||
I want to return "0" based on another cell | Excel Worksheet Functions | |||
formula to return the value of a cell based on a looked up true reference | Excel Worksheet Functions | |||
Returning a Value to a Cell Based on a Range of Uncertain Size | Excel Worksheet Functions | |||
Excel: How to return count for each cell within date range criter. | Excel Worksheet Functions |