I am trying to use the offset function with an indirect lookup for the first variable. (the aim being to populate a graph with a dynamic range calculated using a chosen start date and number of periods).
I have a formula that looks up the value of what needs to be in the reference variable for the OFFSET function:
="'Stat Table'!" & SUBSTITUTE(TEXT(ADDRESS(1,MATCH($Q$1,$4:$4,0),4),""),"1","") & ROW(D6)
(so this just looks up the Letter part that is unique to the chosen date and then the number of the column the offset is for).
I then reference it in the offset like this:
But I get an error message of #VALUE! , any ideas? The offset value works if I just point it directly at the cell like this: =OFFSET(H3,0,0,,'Stat Table'!$Q$2) for example
Please see my problem attached