Row reference as formula.

[Luke777]

Hi,

I’m sure I’ve done this dozens of times before but I’ve forgotten how and can’t seem to find the answer I’m looking for.

=‘Oct23’!X56

I’m trying to make the row reference the result of a formula. X will always be A but the number will change as the formula dictates.

The number I want is actually determined by

=DAYS(NOW(),’Oct23’!A6)*25+6

Which currently results in 56.

Thanks

 

[Joe4]

You use the INDIRECT formula if you are building the range reference dynamically.

 

[etaf]

=INDIRECT("A"&DAYS(NOW(),’Oct23’!A6)*25+6)

i have just used 1 sheet to view easier
1/10/23 - results in 56 - today 3/10/23

Book2
	A	B	C	D
1
2				fred
3
4
5
6
7
8	10/1/23 0:00		56
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56	fred
Sheet1

Cell Formulas
Range	Formula
D2	D2	=INDIRECT("A"&DAYS(NOW(),A8)*25+6)
C8	C8	=DAYS(NOW(),A8)*25+6