Passing cell references from a cell's text to LINEST function and a graph

**DragonQ** · 03-02-2009, 10:31 AM

Hi guys,

I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:

I250 contains: 10, shows: 10
I251 contains: ="I"&I250, shows I10

But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):

Please Login or Register  to view this content.

I figured that maybe it isn't taking "J"&I250 as text and is interpreting it as a direct cell reference, which is why it is failing. If this is true then it might be easy to fix but I don't know how to specifiy a data type as text in Excel.

Oh by the way, this machine is set up to use , as a decimal separator and ; in place of , for separating variables. It's annoying but it's necessary because of another piece of software we use.

Any help appreciated!

**shg** · 03-02-2009, 11:11 AM

You could do something like this:

=LINEST(INDIRECT("J" & I250 & ":J" & J250), INDIRECT("I" & I250 & ":J" & J250), TRUE, TRUE)

However, the x and y ranges overlap.

I251 contains: ="I"&I250, shows I10

I251 isn't referenced.

Using INDIRECTmakes the formula volatile, which means it recalculates whenever anything recalculates. I'd use instead dynamic named ranges, so you could just do

=LINEST(y, x, True, True)

**DragonQ** · 03-02-2009, 11:37 AM

=LINEST(INDIRECT("J" & I250 & ":J" & J250), INDIRECT("I" & I250 & ":J" & J250), TRUE, TRUE)

Thanks, this code works for the LINEST cell with a slight fix:

=LINEST(INDIRECT("J" & I250 & ":J" & J250), INDIRECT("I" & I250 & ":I" & J250), TRUE, TRUE)

I made that error in my first post, sorry. However, I can't get the graph source to accept this code for the x values:

Please Login or Register  to view this content.

Any ideas?

I251 isn't referenced.

Yeah, that cell is just an example of how I got the simple form of the code to work.

Using INDIRECT makes the formula volatile, which means it recalculates whenever anything recalculates. I'd use instead dynamic named ranges, so you could just do

=LINEST(y, x, True, True)

I guess this would be a problem for massive spreadsheets but this is just one sheet of <300 rows and <10 columns so I'm not worried about it recalculating any time anything changes. It's just far easier than going into both the LINEST cell and the graph properties, finding the cell references and changing them manually.

**shg** · 03-02-2009, 11:46 AM

I can't get the graph source to accept this code for the x values:

=INDIRECT("J" & J249 & ":J" & J250)

Bring up the formula auditing toolbar and watch it evaluate.

**DragonQ** · 03-02-2009, 11:57 AM

It gets to:

Please Login or Register  to view this content.

and then fails :/. I thought not using INDIRECT made more sense in this case but that doesn't work either. This works:

Please Login or Register  to view this content.

but then this doesn't:

Please Login or Register  to view this content.

**shg** · 03-02-2009, 12:05 PM

Dunno. Post a workbook.

**DragonQ** · 03-02-2009, 12:24 PM

I just tried editing the series formula from this:

=SERIES(;Sheet1!$I$9:$I$100;Sheet1!$J$9:$J$100;2)

to this:

=SERIES(;Sheet1!XRange;Sheet1!YRange;2)

with the dynamic variables currently evaluating as:

XRange = $I$9:$I$100
YRange = $J$9:$J$100

and it still doesn't work (error is about one or more invalid references).

I have attached a spreadsheet with sample data and the relevent graph, dynamic variables, etc.

**shg** · 03-02-2009, 01:17 PM

This started as a question about LINEST. Is that solved?

**DragonQ** · 03-02-2009, 01:23 PM

The LINEST function works, yes. But the question was:

Passing cell references from a cell's text to LINEST function and a graph

**DragonQ** · 03-03-2009, 06:07 AM

Woo, got it working using the OFFSET function (apparently just using INDIRECT doesn't work for graphs...). I made some dynamic variables for the x values (XRange):

Please Login or Register  to view this content.

And the y values (YRange):

Please Login or Register  to view this content.

And then just put =Sheet1!XRange for the source of the x values for the graph series and then =Sheet1!YRange for the y values.

In this case, Sheet1!$J$249 contains my starting cell row reference and Sheet1!$J$250 contains my ending cell row reference. So if I want the series' x values to be taken from cells I15 to I100 for example, I just put 15 in cell J249 and 100 in cell J250. Obviously you can make the columns dynamic too but in this case I didn't need to.

Thanks for the assistance!