Referencing cells in another worksheet
Interesting info. thans so much
"T. Valko" wrote:
Let's use this small sample to demonstrate:
...........A..........B..........C
1........C..........X.........10
2........F..........D..........20
3........X.........Y..........15
4........A.........C..........30
5........H.........F..........10
Suppose the formula is:
=SUMPRODUCT(--(A1:A5="X"),--(B1:B5="Y"),C1:C5)
Each of these expressions will return an array of either TRUE or FALSE:
(A1:A5="X")
(B1:B5="Y")
(A1=X)=FALSE.....(B1=Y)=FALSE
(A2=X)=FALSE.....(B2=Y)=FALSE
(A3=X)=TRUE.......(B3=Y)=TRUE
(A4=X)=FALSE.....(B4=Y)=FALSE
(A5=X)=FALSE.....(B5=Y)=FALSE
The "--" coerces the TRUE and FALSE to 1 and 0 respectively.
All 3 of the arrays are then multiplied together like this:
0*0*10 = 0
0*0*20 = 0
1*1*15 = 15
0*0*30 = 0
0*0*10 = 0
SUMPRODUCT (the SUM of PRODUCTS) then adds up the result of the array
multiplication to arrive at the result:
=SUMPRODUCT({0;0;15;0;0}) = 15
--
Biff
Microsoft Excel MVP
"xtrout" wrote in message
...
Out of curiosity, what do the dashes do that are before the 2 crieria
ranges
(--)?
"T. Valko" wrote:
You might be able to use something like this:
=SUMPRODUCT(--(Sheet2!A$1:A$100="string_A"),--(Sheet2!B$1:B$100="string_B"),Sheet2!C$1:C$100)
--
Biff
Microsoft Excel MVP
"xtrout" wrote in message
...
Hi All,
I am trying to find a way to link a cell between two worksheets.
Unfortunately copying and pasting the link doesnt work because the data
in
the worksheet I am referencing may change rows. For example...
If I have a string in cells A1 and B1 and a numerical Value in C1 and
then
when I next run the report, records might be added that would move
these
to
say A12, B12 and C12 respectively.
on another worksheet, I have a static report that needs to reference
these
same values even though they may have moved rows.
Basically I need the formula to say that if string in column A equals
one
thing and the string in column B equals on thing, then put the value
from
column C in the cell.
Hope this makes sense and any appreciate any help I can get.
|