Sumif & indirect help please.

[Dan88]

Hi all.
I am trying to use excel to do something and wondering if it is possible.
I am using a sumif function that will sum a reference off another sheet.
I want to use a cell in the current sheet(where the formula exist) to reference the target sheet.
Is this possible.

For instance:
I believe you have to use indirect for to have formula reference a sheet name
I have two sheets, "Win" & "Lose"
On "Win" sheet I have this function =sumif(indirect(J180)G1:G20,"room",E1:E20)
On cell J180 of "Win" tab will be "Lose". I am trying to have the formula reference J180 as part of the function and have it sum anything in the "Lose" tab that has the word "room" in it.

What am I doing wrong?
Thanks in advance.

 

[schielrn]

Maybe you are looking for:

=sumif(indirect(J180&"!G1:G20"),"room",indirect(J180&"!E1:E20"))

Hope that helps.

 

[Mike Blackman]

Hi Dan,

Is this what your after?

=SUMIF(INDIRECT(J180&"!G1:G20"),"room",INDIRECT(J180&"!E1:E20"))

EDIT. I'm Too Late

 

[Dan88]

Mike Blackman/schielrn, thank you very much!

 

[Dan88]

last question.
my "lose" sheet will now be in date format in which i have a macro to name based on this format:
WB.Sheets(WB.Sheets.Count).Name = Format(cell.Value, "MMM-yy")

what do i have to put in cell J180 on "Wins" tab to get formula to work.
i tried formating the day but it does not work.

any insights?

Thanks again

 

[Mike Blackman]

Hi,

Maybe;

=IF(J180="Win",TEXT(SUMIF(INDIRECT(J180&"!G1:G20"),"room",INDIRECT(J180&"!E1:E20")),"0"),TEXT(SUMIF(INDIRECT(J180&"!G1:G20"),"room",INDIRECT(J180&"!E1:E20")),"dd/mm/yyy"))

 

[Mike Blackman]

Or Even,

=IF(J180="Win",TEXT(SUMIF(INDIRECT(J180&"!G1:G20"),"room",INDIRECT(J180&"!E1:E20")),"0")+0,TEXT(SUMIF(INDIRECT(J180&"!G1:G20"),"room",INDIRECT(J180&"!E1:E20")),"dd/mm/yyy")+0)

Due to the coersion of the text back to numbers;

Not sure that SUMIF is the right way to go when working with dates though.

 

[Dan88]

hmm, let me get this correct.
my "lose" sheet has been renamed to Apr-09 based on my vba:
WB.Sheets(WB.Sheets.Count).Name = Format(cell.Value, "MMM-yy")


on my "Win" sheet, i am manually typing in Apr-09 in J180 to match the newly name sheet.

using your formula initially, i am getting a #REF!
but if i rename the sheet to test and type in test in J180, the formula works.
is this a formatting issue?

the lastest function you provided is confusing...
=IF(J180="Win",TEXT(SUMIF(INDIRECT(J180&"!G1:G20"),"room",INDIRECT(J180&"!E1:E20")),"0"),TEXT(SUMIF(INDIRECT(J180&"!G1:G20"),"room",INDIRECT(J180&"!E1:E20")),"dd/mm/yyy"))

because J180 will never be "Win" because that is the name of the sheet.


am i misinterpretting your formula?

Thanks MIKE.

 

[Mike Blackman]

Hi Dan,

So your lose sheet doesn't have a static name? Ignore that last formula then, maybe I'd suggest when your code alters the sheets name that your code also places the new sheet name in another cell, for instance J181, so when J180 says the Indirect references the value in J181 not J180, that way you can get around not having the sheet name static. The formula would then be something like;

=IF(J180="Win",SUMIF(INDIRECT(J180&"!G1:G20"),"room",INDIRECT(J180&"!E1:E20")),SUMIF(INDIRECT(J181&"!G1:G20"),"room",INDIRECT(J181&"!E1:E20")))

 

[Dan88]

Great suggestion.

Thanks for your help Mike. Works like a CHARM!!!