Referencing a Worksheet using a cell reference

This should be a very simple problem. I need this formula:

=SUM('2006'!$D$3:$D$34)

to look for the worksheet name '2006' in the title of the chart, which is cell A1.

The idea is that I want to be able to copy this chart multiple times and only have to change the title of the chart to the new year. I dont want to have to change the year in every single forumula.

Any help is appreciated!
Thanks

Awsome, thanks for your help!

Now I have one more question and my worksheet should be done. I need a formula that will look at cell a1 for what worksheet to go to, then it will search that entire worksheet for a given word, and add all the numbers one cell to the left of that word.

This ones a little harder. I might be able to figure it out now that i know about that indirect formula, but it would take me forever to get it to work.

bump bump bump

Try

=SUMIF(INDIRECT(A1&"!B1:IV65536"),"word",INDIRECT(A1&"!A1:IU65536"))

Note that if your sheet name has no spaces, e.g. 2006 then you can just put 2006 in cell A1 but if sheet name has spaces like Mar 2006 then you'll need to use 'Mar 2006' with the single quotes, otherwise the formula won't work.....or adjust formula to

=SUMIF(INDIRECT("'"&A1&"'!B1:IV65536"),"word",INDIRECT("'"&A1&"'!A1:IU65536"))

Originally Posted by daddylonglegs

Try

=SUMIF(INDIRECT(A1&"!B1:IV65536"),"word",INDIRECT(A1&"!A1:IU65536"))

Note that if your sheet name has no spaces, e.g. 2006 then you can just put 2006 in cell A1 but if sheet name has spaces like Mar 2006 then you'll need to use 'Mar 2006' with the single quotes, otherwise the formula won't work.....or adjust formula to

=SUMIF(INDIRECT("'"&A1&"'!B1:IV65536"),"word",INDIRECT("'"&A1&"'!A1:IU65536"))

I just found another use for this formula for my fleet project. Which of these values can I change to make it go one cell to the right? two to the right? three? Also, instead of just adding all of the values, i want an average.