Sumif (w/o Sheet name)

[Maxwell]

Sometimes when I write a 'Sumif' formula, I pull from
separate workbooks or worktabs. Here is the issue...

usually my sumif formula will look like this (correct
way)...

=Sumif(Sheet2!$a:$a,$z2,Sheet2!$b:$b)

If you look at "$z2" it is the cell I want pulled.

sometimes when I write this formula it does this...

=Sumif(Sheet2!$a:$a,Sheet1!$z2,Sheet2!$b:$b)

the "$z2" is now "Sheet1!$z2".

Both ways work but now if I sort it, the ranges are all
messed up for the "$z2" depending on the row it is sorted
on.

How do I make it so that when I click on a cell "z2" that
it does NOT come up as "Sheet1!$z2".

It's wierd...sometimes it doesn't pull the sheet name and
sometimes it does. Am I doing something wrong?

Please help

 

[Casey]

Maxwell,
Excel automatically insert the sheet name into a formula you ar
creating by selecting the cells and/or ranges, whenever the formula i
referring to a sheet, other than the sheet which holds the formula. Fo
example: If your formula is in cell A1 on sheet 2 and part of th
formula refers to cells on sheet 1, then Excel put the sheetname befor
the range or cell reference.
As to the Z2 criteria cell you mentioned, that somehow gets messed u
when you sort (???? worksheet range or the sheets themselves yo
weren't clear), it is my practice to isolate any cell upon which man
formulas depend, either at the top of my worksheet where they ar
isolated from any sorting action I might use, or on another shee
entirely. to keep it protected.

hope it helps
Case