Excel 2007 : How to address sheets in a function

[samschuer]

Hey guys,

I have a bit of a tricky question about addressing sheets in a function.

What I'm trying to do:
I have one sheet that contains a summary and other subsequent sheets with weekly data (0710, 0717, 0724, etc.).

In order to pull data from the weekly data sheets, I have set up a "sumifs" formula:
=SUMIFS('0710'!$H$1:'0710'!$H$2000,'0710'!$C$1:'0710'!$C$2000,"BlueRider",'0710'!$G$1:'0710'!$G$2000,"1")

The formula goes to sheet 0710 and gets data for "BlueRider" (if the G column = 1).
My goal is to show data for "BlueRider" from every week on my summary sheet.

What I need:
Since new weekly data comes in every week, I need a formula that, when I drag it down in my summary sheet, automatically understands to look at and return data from the next week's sheet (0717 after 0710).

Bottom line: Just like I can allow a field in a formula to be fixed or changeable when I drag down a formula, I need to do the same for sheets. How does it work?

Your help is greatly appreciated.

Best,
Sam

[Paul]

Hi Sam, welcome to the forum.

Have a quick look around for the INDIRECT function with sheet names. You should see a number of examples on this forum and the web. If you still need assistance after searching be sure to let us know!