how to adjust chart series formula to include the "indirect" function

I posted this in the XL General forum a while ago, but looks like no one knows the answer......

Is there a way to use VB somehow to adjust the chart to achieve what I'm looking for?

Currently I have an "xy" chart setup so that it reads values from a column of data that can "flow". That is, each value of data is read from another workbook like this:

Please Login or Register  to view this content.

Thus, if I change the value in cell AG2, from 0 to 10 for example, the value that is returned is now from 10 rows lower in the tab whose name is in cell AG8, and that tab is located in worksheet who's name is in cell BA2.

What I would like to do is instead of having a column of data in the same workbook as the chart, elliminate that column of data and all of the "indirect" functions that bring it into the workbook that contains the chart from the source workbook that contains the data (this takes a lot of memory), and instead have the "indirect" statement in the chart series formula itself.

For example: in my current chart if I was to click on a series, I would see the following formula in the formula bar:

Please Login or Register  to view this content.

Is it possible to use the "indirect" function in this formula to achieve what I am able to achieve now?

Thank you.

You cannot use formulas in a SERIES formula directly. You would have to define names using the INDIRECT formulas and then use those names in the SERIES formula itself.

You would have to use named ranges. If it's always the same source workbook, then you can define the names in that workbook. If not, you'll need to define the names in the chart workbook.

OK, interesting idea. Let's say I name a range in the source sheet. Is there a way to chage what range the named range reffers to without using VB? Or how would I cycle through the data? My source workbook has over 3000 rows and I need to look at it 50 or so at a time, but in small incerements. So the first incerement would be rows 1-50, then next rows 2-51, then 2-52 and so on all the way down.

Thanks

Yes - you can actually use INDIRECT in the name definition as you were in the original formula.

Luv,

with your data in column A, have a cell

B1 that determines where to start the charting
C1 that determines how many rows to include in the chart

So, B1 = 100 and you want your chart to show data in 50-row chunks, so C1 = 50

Create a dynamic range name, (Insert - Name - Define) call it MyRange, and let it refer to

=Index(A:A,$B$1,$B$1+$C$1)

If B1 = 100, the range MyRange will be B100:B150

Use MyRange in the chart series definition, instead of the static range.

Change the value in B1 or C1 and your chart will change dynamically.

cheers

Did you mean:
=Index(A:A,$B$1):INDEX(A:A,$B$1+$C$1)
?

yes, I did. Just checking if you were following the thread.

LOL.

Now while you all go watch the opening game, I'll study the inside of my eyelids. PM me with the score.

cheers

Doesn't start until 3pm. (hopefully my hangover may have lifted a little by then)

A followup question:

I'm working in workbook_1 and workbook_2 is a data workbook which has the data I need to be able to work with.

I have the following named range (call it MyRange) set up like this within workbook_1:

=INDEX([workbook_2.xlsx]sheet_1!$B$2:$B$3500,$A$1):INDEX([workbook_2.xlsx]sheet_1!$B$2:$B$3500,$A$1+180)

What I'd like to do without VB is have a cell in workbook_1 that changes it's value from sheet_1, to sheet_2, to sheet_3 and so on, and have MyRange automatically updated to refer to these appropriate sheets. Is there a way of doing this without using INDIRECT function or VB?

Thanks

If the cell holds the worksheet name as a text string, then you need either VBA or Indirect() to use that sheet in a cell reference.

OK, thank you. I ended up using INDIRECT and set up an array in several sheets and INDEX in others. Vs. my original method, arrays speed up things a ton!