Symbolic referencing of ranges

**eamonnwalker** · 05-23-2012, 07:34 AM

I was wondering if it is possible to define a range of data in Excel 2007 by using row numbers defined by the values of specific cells.

I have a large number of data files that I need to plot and analyse to retrieve some linear regression data. Basically, each file is a long list of data points (i,x,y for each point). In each file, there are two regions where the data is more or less linear, and I use the LINEST function to retrieve the data for the two curve fits (slope, intercept, and uncertainty of each). The files are formatted exactly the same, exept the data are different, and (unfortunately), the indices of the linear ranges are different in each one.

I was hoping to save some time by not having to manually edit the four numbers in each of the eight cells for each of the dozens of Excel files, but I don't know if this is possible in Excel or not. I have not been able to find anything that will do what I would like.

Basically, right now, my cells look something like this, for example:

Please Login or Register  to view this content.

I have found that I can give names to the ranges I want, which is already an improvement over the way I have it now. However, I still have to manually select the cells I want and define the name. I was wondering if there were a way to type, for example, 448 into one cell ($F$4 to be precise), type 576 into another ($F$5), and have the above LINEST function use the range defined by these row numbers. This way, for each new file, all I would have to do is copy in the appropriate data, manually change the numbers in the two cells, and the rest would be automatic. Is this possible?

Enormous thanks to anyone who can help me here.

By the way, I have never used macros in Excel before, but I'm perfectly comfortable with programming (Fortran, VBasic, Pascal, ...), so if it's necessary and not too complicated, I'm willing to give it a shot. Thanks again.

- Eamonn

**Cutter** · 05-23-2012, 08:05 AM

You can use the INDEX() function

=INDEX(LINEST(INDEX($C:$C,$F$4):INDEX($C:$C,$F$5),INDEX($B:$B,$F$4):INDEX($B:$B,$F$5),TRUE,TRUE),2,2)

**eamonnwalker** · 05-23-2012, 08:29 AM

Thanks, that's a big help.

Incidentally, I copied the code into the definition of the names in the Name Manager, and that allowed me to eliminate some clutter and automatically plot the two subsets, as well. Thanks again!

- Eamonn