Indirect within a formula

[CBoukal]

I read an old post on using indirect within a formula but it doesn't really work. I am trying to use the text of a cell within the formula below. B2 is a sheet name within the workbook. Rather than retyping the sheet name 60 times or using the search replace for Sheet, is there a way to make this work?
=COUNTIF(INDIRECT(B2)!$H$3:INDIRECT(B2)!$H$500, "Win")

 

[dreid1011]

You have to write all of the range inside the INDIRECT():

=COUNTIF(INDIRECT(B2&"!$H$3:"&B2&"!$H$500"), "Win")

 

[CBoukal]

You have to write all of the range inside the INDIRECT():

=COUNTIF(INDIRECT(B2&"!$H$3:"&B2&"!$H$500"), "Win")

Thank you for that, it makes sense now but I get #REF! error. It works fine when its typed out.

 

[CBoukal]

This gives me #ref also. But If i take the indirect out it works fine.
=COUNTIF(INDIRECT("Ethan!$H$1:Ethan!$H$500"), "Loss")

 

[dreid1011]

Thank you for that, it makes sense now but I get #REF! error. It works fine when its typed out.

That's what I get for trying to write it out outside of Excel.

=COUNTIF(INDIRECT(B2&"!$H$3:$H$500"), "Win")

 

[dreid1011]

This gives me #ref also. But If i take the indirect out it works fine.
=COUNTIF(INDIRECT("Ethan!$H$1:Ethan!$H$500"), "Loss")

Because the sheet name only needs to be at the start of the reference, not in the middle too. That was my mistake why the first one gave you the error. See my fix above.

 

[CBoukal]

Because the sheet name only needs to be at the start of the reference, not in the middle too. That was my mistake why the first one gave you the error. See my fix above.

You are awesome thank you very much!!!!

 

[dreid1011]

You are awesome thank you very much!!!!

You're welcome, I am happy to help.