So strange! Why is my INDIRECT function returning 0 even though the formula should be 100% correct?

[jvs411]

I'm trying use INDIRECT into my formula but for some strange reason, it just doesn't work even though the alternative without INDIRECT does.
This is how my INDIRECT formula looks like (my sheet name is called "JAN HH")

The cell references:

And this is the sheet, "JAN HH", I'm trying to retrieve the value of 61 from column T

The funny thing is, if I removed my INDIRECT functions from the formula, it DOES work

So what's the issue here? I just doesn't seem to make sense at all to me.
It's worth noting also that the data in my "JAN HH" sheet is also linking to another external workbook using the INDIRECT function. Does this linkages have something to do with it? The linkage to the external workbook works fine though, as seen with all the values displayed.

 

[jvs411]

Also another strange thing is that my non-INDIRECT formula as seen above also displays as 0 if I first input the formula. I have to go into the formula bar and press enter again for the 61 to display...

 

[Peter_SSs]

Try like this

jvs411.xlsm
	C	D	E
1
2	JAN		61
3	HH
4	BD1
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMIF(INDIRECT("'"&C2&" "&C3&"'!B:B"),""&C4&"",INDIRECT("'"&C2&" "&C3&"'!T:T"))

 

[jvs411]

Wow that worked! But...how?

I did a comparison between my formula and yours and it seems this red highlighted part is the main difference?
=SUMIF(INDIRECT("'"&C2&" "&C3&"'!B:B"),""&C4&"",INDIRECT("'"&C2&" "&C3&"'!T:T"))

Why is it when I typed this as INDIRECT(C4) instead, it doesn't work?

 

[Peter_SSs]

Why is it when I typed this as INDIRECT(C4) instead, it doesn't work?

Because INDIRECT(C4) does not turn what is in C4 into a string as required, instead it says to go to cell BD1 and use the value there as the SUMIF condition.
If you go to cell BD1 on the formula sheet and enter DB1 there again, you would fine that formula with INDIRECT(C4) works.
However, turning BD1 into a string directly as I did is much more logical (and INDIRECT ).

 

[jvs411]

Because INDIRECT(C4) does not turn what is in C4 into a string as required, instead it says to go to cell BD1 and use the value there as the SUMIF condition.
If you go to cell BD1 on the formula sheet and enter DB1 there again, you would fine that formula with INDIRECT(C4) works.
However, turning BD1 into a string directly as I did is much more logical (and INDIRECT ).

Yeah I realized that now... Also seems I can just type C4 instead of ""&C4&"" to get the same result. Thanks for the help!

 

[Peter_SSs]

Also seems I can just type C4 instead

Yes, that makes even more sense.

Thanks for the help!

You're welcome.