Referencing cells in another worksheet

[xtrout]

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.

 

[T. Valko]

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)

 

[xtrout]

That works. Thanks a bunch

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)

 

[xtrout]

Out of curiosity, what do the dashes do that are before the 2 crieria ranges
(--)?

 

[T. Valko]

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

 

[xtrout]

Interesting info. thans so much

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

 

[T. Valko]

You're welcome. Thanks for the feedback!