Formula help

[VegasNath]

A17: Date 15-Apr-10
C17: ='15-04'!D6

How can i amend the formula in C17 to incorporate the date in A17? Along the lines of.....

='TEXT(A17,"dd-mm")'!D6

[HansV]

Try

=INDIRECT("'"&TEXT(A17,"dd-mm")&"'!D6")

[VegasNath]

Thankyou.

[VegasNath]

How can I combine these 2 together into one sum?

=IF(ISERROR(INDIRECT("'"&TEXT($A30,"dd-mm")&"'!E50"))=TRUE,0,INDIRECT("'"&TEXT($A30,"dd-mm")&"'!E50"))
=IF(ISERROR(INDIRECT("'"&TEXT($A30,"dd-mm")&"'!E56"))=TRUE,0,INDIRECT("'"&TEXT($A30,"dd-mm")&"'!E56"))

[HansV]

Does this do what you want?

=IF(ISERROR(INDIRECT("'"&TEXT($A30,"dd-mm")&"'!E50")),0,INDIRECT("'"&TEXT($A30,"dd-mm")&"'!E50"))+IF(ISERROR(INDIRECT("'"&TEXT($A30,"dd-mm")&"'!E56")),0,INDIRECT("'"&TEXT($A30,"dd-mm")&"'!E56"))

[VegasNath]

Yes, apologies for not posting back earlier, I figured it out soon after posting. Cheers.

[VegasNath]

=IF(ISERROR(INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G51"))=TRUE,0,INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G51"))+IF(ISERROR(INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G52"))=TRUE,0,INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G52"))+IF(ISERROR(INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G53"))=TRUE,0,INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G53"))+IF(ISERROR(INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G54"))=TRUE,0,INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G54"))+IF(ISERROR(INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G55"))=TRUE,0,INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G55"))

This is summing a range of 5 cells based on one criteria, so I am suspecting that there is a simpler solution?

[HansV]

It depends - do you really need to check each individual cell for errors? Could you fix the values/formulas on the sheet that you refer to, so that they don't return error values?

[VegasNath]

This is where I think my set-up may be a little wrong. The formula above provides the correct result, however, I will try and explain the actual requirement.

I need to sum the range G52:G55 on the sheet "TEXT($A14,"dd-mm")"
It is not possible that any cell within that range could return an error value.
My only requirement to suppress error values is that the worksheet "TEXT($A14,"dd-mm")" may not actually exist.

So, I need to check that "TEXT($A14,"dd-mm")" exists, if not return 0, else sum the range.

Hope this makes sense. Thanks

[HansV]

Your formula appears to indicate that you want to sum G51:G55, not G52:G55. You can use this shorter formula:

=IF(ISERROR(INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G51")),0,SUM(INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G51:G55")))

[VegasNath]

You can use this shorter formula:

Thanks very much, that is much easier to understand.

Can I throw in a final spanner... In the same situation, how would I sum a non-continuous range, say (G51:G54, G59, G62:G63) ?

[HansV]

It becomes more complicated again:

=IF(ISERROR(INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G51")),0,SUM(INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G51:G54"),INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G59"),INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G62:G63")))

It'd be easier if you could sum the values on the sheet referred to. Let's say that G66 on the sheet referred to contains the formula

=SUM(G51:G54,G59,G62:G63)

You could then use

=IF(ISERROR(INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G51")),0,INDIRECT("'"&TEXT($A14,"dd-mm")&"'!G66"))

[VegasNath]

Great, Thanks Hans.

[VegasNath]

Another that I cannot get my head around:

=IF(ISERROR(VLOOKUP($A117,'Consol'!$A$4:$AP$33,3,0)=TRUE),0,VLOOKUP($A117,'Consol'!$A$4:$AP$33,3,0)+VLOOKUP($A117,'Consol'!$A$4:$AP$33,19,0)+VLOOKUP($A117,'Consol'!$A$4:$AP$33,32))

This should be checking that the value of A117 exists in Sheet 'Consol' in column A. If not, 0, else:
Sum 'Consol' (found row) columns C, S & AF.

Is there a more efficient approach to this? (Other than summing on the consol sheet, which I am unable to do in this scenario).

[HansV]

Not really.