Let’s assume, you got 12 worksheets named with year and month. Each sheet contains several values for every day. To put them all together I recommend a combination of the Excel functions CONCATENATE and INDIRECT (the real dynamic duo if there wasn’t one before).

The training workbook
It consists of three sheets named “work”, “Sheet1” and “Sheet2”. “Sheet1” and “Sheet2” hold simple data.

The initial training workbook

The INDIRECT function
A cell reference to another sheet contains the sheet’s name followed by “!” (without the quotes) and the cell reference (e.g. B12 for 2nd cloumn and 12th row).
You can get this data by using INDIRECT(ref_text) with a string as ref_text. This string can come from another cell as shown below.

using INDIRECT to get data

As you can see, it’s the same data as with a direct reference. Well, not that exciting yet.

The CONCATENATE function
This functions adds strings to one longer string. I already used the function in the budget sheet. With CONCATENATE(text1,text2,…) you can built strings the way you need them.
I renamed “Sheet1” and “Sheet2” to “200901” and “200902”. In “work” the first column now holds the date. It doesn’t matter which kind of format, we’ll extract what we need per function.
The following function is added to cell B2, while A2 holds the date.

=INDIRECT(CONCATENATE(YEAR(A2),"0",MONTH(A2),"!B3"))

YEAR extracts the year value of A2 (2009), MONTH does so for the month (1), “0” and “!B3” are neccessary for building and referencing correct. So, CONCATENATE will return 200901!B3 which exactly looks like a direct reference. And in combination with INDIRECT it returns the correct value! With a fast fill down (drag the little square at the bottom right down over as many cell as you want to be filled with the formula) it does so for the third row!

The working worksheet

While there is no sheet for March the cell returns a #REF, as it does for the direct link (manually put in =200903!B3 because there’s no sheet to click in).

The power of dynamic references
Once I insert a worksheet and rename it to “200903” the indirect reference gives back a value while the direct link still doesn’t work. Select this cell, enter the formula bar (clicking or F2) and hit ‘enter’, then the direct link is some kind of reevaluated.

dynamic reference

The other way round, deleting “200903” completely destroys our reference there. The INDIRECT thing will work again once a sheet “200903” is inserted again, the direct one won’t.

Uhh, devastation in references...

To conclude: you can use the indirect for complex formulas as well. If the formula was
=SUM(INDIRECT(CONCATENATE(YEAR(A2),"0",MONTH(A2),"!","B:B")))

it sums up every value in column B on the referenced worksheet.

The summary.

That works for everything you most likely need, e.g. COUNTIF, COUNTA, SUMIF… . If there are open questions just let me know.

That’s t’ power o’ t’ shooting below t’ waterline. Yo ho!