Reference formula

[MJ Erasmus]

Hi,

I have a spreadsheet that I capture data in on a daily basis with a lot of different "Tables" which aren't formatted as actual tables.

I then reference to select data to other tables in the sheet which are not in the same layout which is where my knowledge in excel is being tested.

the base data that I use is from the table below

20-01-23.xlsx
	N	O	P	Q	R	S	T
13	Wip_Ageing	0-29	30-59	60-89	90-120	>120	Sum
14	21 Oct	29	10	7	3	10	59
15	24 Oct	31	8	7	3	10	59
16	25 Oct	31	8	7	3	10	59
17	26 Oct	31	7	6	4	9	57
Data

Cell Formulas
Range	Formula
T14:T17	T14	=SUM(O14:S14)

I then compare 3 parts of the above table in a separate table as seen below and is where I am getting stuck with semi-automating it with cell references.

20-01-23.xlsx
	I	J	K	L
2	Row:	26	52
3	Wip_Ageing	Wip Age 08 Nov	Wip Age 13 Jan	30 Day Average
4	0-29 Days	35	5	17
5	30-59 Days	11	17	16
6	60-89 Days	5	15	7
7	90-120 Days	4	3	3
8	>120 Days	9	12	10
Data

Cell Formulas
Range	Formula
K3	K3	=CONCATENATE("Wip Age ",N52)
J3	J3	=CONCATENATE("Wip Age ",N26)
J4	J4	=O26
J5	J5	=P26
J6	J6	=Q26
J7	J7	=R26
J8	J8	=S26
L4	L4	=O57
L5	L5	=P57
L6	L6	=Q57
L7	L7	=R57
L8	L8	=S57

The 30 Day average portion works fine as I manually move the row down every day when adding the new data.

my goal is to be able to change the numbers above the table to the row number and have the data then be referenced to that row number,
I have tried multiple formulas and nested formulas to try and get the cell reference to work.

some examples that I tried
=CONCATENATE("=O",J2)
=CELL("contents",CONCATENATE("O",J2))

 

[MJ Erasmus]

Got it working by using the Index formula. Now I can simply change the row numbers and the data will auto populate

20-01-23.xlsx
	I	J	K	L
2	Row:	14	44
3	Wip_Ageing	Wip Age 08 Nov	Wip Age 19 Jan	30 Day Average
4	0-29 Days	35	5	17
5	30-59 Days	11	17	16
6	60-89 Days	5	15	7
7	90-120 Days	4	3	3
8	>120 Days	9	12	10
Data (2)

Cell Formulas
Range	Formula
J3	J3	=CONCATENATE("Wip Age ",INDEX($N$13:$T$117,$J$2,1))
K3	K3	=CONCATENATE("Wip Age ",INDEX($N$13:$T$117,$K$2,1))
J4	J4	=INDEX($N$13:$T$117,$J$2,2)
K4	K4	=INDEX($N$13:$T$117,$K$2,2)
L4	L4	=O57
J5	J5	=INDEX($N$13:$T$117,$J$2,3)
K5	K5	=INDEX($N$13:$T$117,$K$2,3)
L5	L5	=P57
J6	J6	=INDEX($N$13:$T$117,$J$2,4)
K6	K6	=INDEX($N$13:$T$117,$K$2,4)
L6	L6	=Q57
J7	J7	=INDEX($N$13:$T$117,$J$2,5)
K7	K7	=INDEX($N$13:$T$117,$K$2,5)
L7	L7	=R57
J8	J8	=INDEX($N$13:$T$117,$J$2,6)
K8	K8	=INDEX($N$13:$T$117,$K$2,6)
L8	L8	=S57