how to reference a function from a different sheet

[xianwinwin]

Hi all,

I'm facing such a trivial question for couple of hours and wonder if anyone can assist here...

I have a the following function:
=INDIRECT(ADDRESS(SUMPRODUCT((F35:G45=K40)*ROW(F35:G45))-21,SUMPRODUCT((F35:G45=K40)*COLUMN(F35:G45))))

I'm trying to put the get the value of it from a different Sheet.
say this function is in Sheet1 and I wish to place the equation in Sheet3 - what should I change?

I tried INDIRECT(ADDRESS(SUMPRODUCT((Sheet1!F35:G45=K40)...
nothing works.


what the function does?
there are 2 tables
table 1 has values and table 2 has values as well
K40 is the user input --it reflects the number in table2 and the return value is the 'reference' in table1

for example, if table2 in F41 has the number 54 and the reference for it in table1 is $344, when the user types 54 in K40 the returned result in the function will be $344

Thank you for any pointers!

[daddylonglegs]

Given that you are returning numeric values try

=SUMIF(F35:G45,K40,F14:G24)

If the referenced ranges are in sheet1! then change to

=SUMIF(sheet1!F35:G45,K40,sheet1!F14:G24)

which assumes that K40 is in the same sheet as the formula

[xianwinwin]

mmm....this is what I had in mind initially...didn't work.

This is the function I re-constructed:

=INDIRECT(ADDRESS(SUMPRODUCT((Contribution!F35:G45=U6)*ROW(Contribution!F35:G45))-21,SUMPRODUCT((Contribution!F35:G45=U6)*COLUMN(Contribution!F35:G45))))

Where Contribution! is the name of the sheet
AND U6 is the user's input.

any idea?

[daddylonglegs]

You need to include the sheet name as text as the 5th argument of ADDRESS function like this

=INDIRECT(ADDRESS(SUMPRODUCT((contribution!F35:G45=U6)*ROW(contribution!F35:G45))-21,SUMPRODUCT((contribution!F35:G45=U6)*COLUMN(contribution!F35:G45)),,,"Contribution"))

but won't this formula give you the same result?

=SUMIF(contribution!F35:G45,U6,contribution!F14:G24)

For both, U6 referred to is on the same sheet as the formula

[xianwinwin]

Thank you so much!