Referencing worksheets

Hello Niecie, welcome to the board!


Sounds like a good job for the function THREED (Three-D) that you get when you download/install the morefunc add-in. You can download here and find additional information here.


HTH

Confused

Okay so now I am really confused. I am not familiar with this at all. Can you help me a little more? I downloaded the link you sent but now what do I do. I am very uncomfortable with this. I can write formulas all day long but this is out of my realm. HELP!!!!

There is no native function in Excel that will let you do a formula like:

=sum(A1 of sheet1 through sheet3)

What Zack is suggesting is to download the morefunc add-in which, just like the analysis toolpak, adds additional formulas to Excel. One of these is THREED, which will allow you to do a "of sheets 1 through 3" type formula.

If you don't want to go down that road, you can try
=SUMIF(Sheet1!$F:$F,A37,Sheet1!$M:$M) + SUMIF(Sheet2!$F:$F,A37,Sheet2!$M:$M) + SUMIF(Sheet3!$F:$F,A37,Sheet3!$M:$M)

Or, list each cell in its own cell and then sum them.

You can use the indirect formula to write formulas as text [handy if you have sheet names listed in a column and want your formula to make =sumif(indirect(A1&"!$F:$F"),A37,indirect(A1&"!$M:$M))].

But, you really have 3 options:
1) Zack's add-in
2) three different sumif formulas
or 3) VBA.

Willing to Learn

I am willing to learn Zack's way I just need a little more guidance (hand holding)

You wouldn't be able to use the entire column, and you'd have to make your ranges the same size. And then you could adapt something like this ...


=SUMPRODUCT(--(THREED('[Contract Status Report.xls]2003:2004'!$F$1:$F$500)=B1),(THREED('[Contract Status Report.xls]2003:2004'!$M$1:$M$500)))


The two (possibly more) sheets here are assumed to be '2003' and '2004'. Now this formula will look at sheet '2003' as the left-most sheet and sheet '2004' as the right-most sheet; so everything in between these two sheets will also be looked at. It is determined by sheet position.


HTH