Admin
1
The cell d2 contains a number ,suppose it is 4.
I want to set the offset's reference as a5.
"a"&text(d2+1,0) can express string a5,i want to call data in range from a5 until a9.
OFFSET("a"&text(d2+1,0),0,0,5,1)
Why offset function in excel can't accept "a"&text(d2+1,0) as a5?
How to fix the offset function expression in excel?
use the INDIRECT function if you want to convert a string into a reference,
INDIRECT(ref_text, [a1])
so in your example:
OFFSET(INDIRECT("a"&text(d2+1,0)),0,0,5,1)
see https://support.office.com/en-us/article/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261 for more details
However given offset takes,... offsets! you could just have a1 as the base and use the 4 to actually specify an offset., ie
OFFSET(a1,d2,0,5,1)
