Row number as text which gets used to create formulas

[BillPeterson]

Hi, I want to enter a row number in one column, which is then used to create formulas pointing to cells in that row. Any hints? So far no luck.

Background for those interested: I have a financial spreadsheet in which today's data always goes on the last free row. Then several formulas copied down are taking today's entry and calculating how much it's changed since some earlier date (say beginning of a new year or after a redistribution). Whenever it's time to set the new starting row I have to update like 30 formulas which is annoying. Seems like there ought to be a way to just say ok from here on out, all those formulas are going to use this new row number (in a single cell) as the starting point. For example, one of the formulas is simply returning the difference between today (current row) and the starting point (earlier row).

=A100-A$1

For example, instead of me manually having to update the "A$1" to "A$5" or whatever like every quarter, I'd like Excel to get the row variable from another column which contains just "X" and then create the new cell reference automatically "A$X". That way I can just update the row number every quarter and have all the difference and %chg columns use that.

This page is awesome thanks in advance for your for your help!!!!

 

[BillPeterson]

How about

+Fluff 1.xlsm
	A	B	C	D
1	stock price	ref row	change	%change
2	100	2	0	0%
3	99	2	-1	-1%
4	101	2	1	1%
5	100	2	0	0%
6	102	2	2	2%
7	101	2	1	1%
8	103	2	3	3%
9	102	2	2	2%
10	104	10	0	0%
11	105	10	1	1%
12	106	10	2	2%
Main

Cell Formulas
Range	Formula
C2:C12	C2	=A2-INDEX(A:A,B2)
D2:D12	D2	=C2/INDEX(A:A,B2)

Nice! And based on what I've been reading INDEX is less volitile, correct?

 

[BillPeterson]

Thank you both so much! This board is the best.

 

[Fluff]

based on what I've been reading INDEX is less volitile, correct?

Index is non-volatile, rather than less volatile.

Glad we could help & thanks for the feedback.