There are so-called 3-D formulas to summarize similarly structured data spreading across multiple sheets. For example, I used the following formula to sum up the values of the same cells B15 held by 4 worksheets ranging from Sheet1 through Sheet5:
sum(Sheet1:Sheet5!B15)
In case I want to refer a daynmaic range moving down from B15 to B16, B17...etc in different scenario, how to replace the hot code "B15"?
Hello,
Have you read this article? If you for example reference to a named cell where you type in what cell(s) you want to summarize it will make it semi dynamic.
Check out the article and it will surely give you some hints.
Br,
Anders
Hi Anders,
I tried to define a named range "pos" which refers to "F"&MATCH(TODAY(),Table[date],0)+1
then wrote the 3-D formula as SUM("Sheet1:Sheet5!"&indirect("pos"))
I jthought I could assemble the reference as text, and have Excel treat the text as a reference by using formula INDIRECT as shown above. However, it didn't work as I expected. I guessed it could be something related to syntax error, am I right? Would you like to give me a clear guide. Thanks.
Br,
Julian
