Need Solution

  • Thread starter Thread starter aresar
  • Start date Start date
A

aresar

Sheet 1 Listing the number referenced are in columns A & B
2 1
3 2
4 3

2a 2
2b 2a

Sheet 2 Listing the numbers are all in column A
1
2
3
4

2
2a
2b

(Assume the order on both sheets CANNOT be sorted or changed in an
way)

in cell SHEET 1 C1 and down in that column:

SUMPRODUCT(('SHEET2'!$A$2:$A$501=A1)*('SHEET2'!$A$1:$A$500=B1)*'SHEET2'!$C$1:$C$500))

The above formula is in my SHEET 1 and it references data from SHEET
(just to simplify the explaination).

then in SHEET 2 D1 the Function is

SUMPRODUCT(('SHEET1'!$B$1:$B$121=A1)*('SHEET1'!$A$1:$A$121=A2)*('SHEET1'!$C$1:$C$121))

For some reason the function in SHEET 2 keeps giving me #VALUE for a
answer. Can anyone tell me why?

The reason the sheets are set up like this is the user of th
spreadsheet will be looking at the data in SHEET 1 and could possibl
want to change it with a manual key-in. I will be looking at SHEET
after they are done and see C1 and D1 side by side. If the use
changed the number, D1 should show the new number the user keyed-in an
C1 be the original number
 
Not really sure .. but maybe try these slightly corrected
formulas ? You'll have to confirm that the returns are ok
with some test data

In Sheet1
---------
In C1:

=SUMPRODUCT((Sheet2!$A$1:$A$500=A1)*(Sheet2!$B$1:$B$500=B1)
*(Sheet2!$C$1:$C$500))

Copy down col C

In Sheet2
---------

In D1:

=SUMPRODUCT((Sheet1!$B$1:$B$121=A1)*(Sheet1!$A$1:$A$121=A2)
*(Sheet1!$C$1:$C$121))

-------
Some notes on the original formula
in Sheet1, C1 from your post
-------
SUMPRODUCT(('SHEET2'!$A$2:$A$501=A1)*('SHEET2'!
$A$1:$A$500=B1)*'SHEET2'!$C$1:$C$500))

The range $A$2:$A$501 doesn't tally with the other 2
ranges, i.e. correction could be $A$1:$A$500

and also

$A$1:$A$500=B1 seems "corrected"
if it's $B$1:$B$500=B1 instead

--
Rgds
Max
xl 97
 
Back
Top