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!!!!

 

[jtakw]

Hi,

You mean this?

Book3.xlsx
	A	B
19	18	82
Sheet1050

Cell Formulas
Range	Formula
B19	B19	=100-INDIRECT("A"&19)

 

[jtakw]

On second thought, may be you mean this:

Book3.xlsx
	A	B
18	55
19	A18	45
Sheet1050

Cell Formulas
Range	Formula
B19	B19	=100-INDIRECT(A19)

 

[BillPeterson]

Sorry my original post might have been confusing. For example below, I'd like the formulas in columns C and D to use the "ref row" information. Notice how on row 10 the formulas in columns cell C and D start using A$10 instead of A$2. I've been manually updating these formulas and now want to just use the information in Column B to create those cell references (not even sure this is the right term, but basically trying to say I want to create a variable cell location based on Column B instead of a fixed cell location requiring significant maintenance). How do I tie the formulas in columns C & D to use the information in Column B to do this?

Finances.xlsx
	A	B	C	D
1	stock price	ref row	change	%change
2	$ 100	2	$ -	0%
3	$ 99	2	$ (1)	-1%
4	$ 101	2	$ 1	1%
5	$ 100	2	$ -	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%
11	$ 105	10	$ 1	1%
12	$ 106	10	$ 2	2%
Workspace

Cell Formulas
Range	Formula
C2:C9	C2	=A2-A$2
D2:D9,D12	D2	=C2/A$2
C10:C12	C10	=A10-A$10
D10:D11	D10	=C10/A$10

 

[jtakw]

The method in Post #3 should work:

Book3.xlsx
	A	B	C	D
1	stock price	ref row	change	%change
2	100	A2	0	0%
3	99		-1	-1%
4	101		1	1%
5	100		0	0%
6	102		2	2%
7	101		1	1%
8	103		3	3%
9	102		2	2%
10	104	A10	0	0%
11	105		1	1%
12	106		2	2%
13
14	stock price	ref row	change	%change
15	100	A2	0	0%
16	99	A2	-1	-1%
17	101	A2	1	1%
18	100	A2	0	0%
19	102	A2	2	2%
20	101	A2	1	1%
21	103	A2	3	3%
22	102	A2	2	2%
23	104	A10	0	0%
24	105	A10	1	1%
25	106	A10	2	2%
Sheet3

Cell Formulas
Range	Formula
C2:C9	C2	=A2-INDIRECT(B$2)
D2:D9	D2	=C2/INDIRECT(B$2)
C10:C12	C10	=A10-INDIRECT(B$10)
D10:D12	D10	=C10/INDIRECT(B$10)
C15:C25	C15	=A15-INDIRECT(B15)
D15:D25	D15	=C15/INDIRECT(B15)

 

[BillPeterson]

On second thought, may be you mean this:

Book3.xlsx
	A	B
18	55
19	A18	45
Sheet1050

Cell Formulas
Range	Formula
B19	B19	=100-INDIRECT(A19)

Thank you yes getting closer. I've tried using INDIRECT in the past which is definitely useful. Do I also need column info? Or is there a way to take a row number in a cell reference and make it a variable?

 

[BillPeterson]

The method in Post #3 should work:

Book3.xlsx
	A	B	C	D
1	stock price	ref row	change	%change
2	100	A2	0	0%
3	99		-1	-1%
4	101		1	1%
5	100		0	0%
6	102		2	2%
7	101		1	1%
8	103		3	3%
9	102		2	2%
10	104	A10	0	0%
11	105		1	1%
12	106		2	2%
13
14	stock price	ref row	change	%change
15	100	A2	0	0%
16	99	A2	-1	-1%
17	101	A2	1	1%
18	100	A2	0	0%
19	102	A2	2	2%
20	101	A2	1	1%
21	103	A2	3	3%
22	102	A2	2	2%
23	104	A10	0	0%
24	105	A10	1	1%
25	106	A10	2	2%
Sheet3

Cell Formulas
Range	Formula
C2:C9	C2	=A2-INDIRECT(B$2)
D2:D9	D2	=C2/INDIRECT(B$2)
C10:C12	C10	=A10-INDIRECT(B$10)
D10:D12	D10	=C10/INDIRECT(B$10)
C15:C25	C15	=A15-INDIRECT(B15)
D15:D25	D15	=C15/INDIRECT(B15)

Thank you. But is there a way to do that without the column information without adding it? Ideally I just want to update the row number. There are about 50 sets of columns C and D from the example above and they are all looking at information from different columns (not just A).

 

[jtakw]

I'm afraid I can't think of a way to use a cell reference as the row variable to reference another cell...

 

[Fluff]

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)

 

[jtakw]

Thought of OFFSET, but I like Fluff's INDEX better:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	stock price	ref row	change	%change		stock price	ref row	change	%change
2	100	2	0	0%		100	2	0	0%
3	99		-1	-1%		99	2	-1	-1%
4	101		1	1%		101	2	1	1%
5	100		0	0%		100	2	0	0%
6	102		2	2%		102	2	2	2%
7	101		1	1%		101	2	1	1%
8	103		3	3%		103	2	3	3%
9	102		2	2%		102	2	2	2%
10	104	10	0	0%		104	10	0	0%
11	105		1	1%		105	10	1	1%
12	106		2	2%		106	10	2	2%
Sheet9

Cell Formulas
Range	Formula
C2:C9	C2	=A2-OFFSET(C2,B$2-ROW(),-2)
D2:D9	D2	=C2/OFFSET(C2,B$2-ROW(),-2)
C10:C12	C10	=A10-OFFSET(C10,B$10-ROW(),-2)
D10:D12	D10	=C10/OFFSET(C10,B$10-ROW(),-2)
H2:H12	H2	=F2-OFFSET(H2,G2-ROW(),-2)
I2:I12	I2	=H2/OFFSET(H2,G2-ROW(),-2)