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.

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.

That works. Thanks a bunch

"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.

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.

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.

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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"xtrout" wrote in message
...
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.