Sumif Range Problem

[GPnew]

The following formula works =SUMIF(Month,"<"&MonthNumber,INDIRECT(V23)) where cell v23 contains the following concatenation
="'Food Last Year'!$C" & MATCH(B$23,LYIndex,0) & ":$N" & MATCH(B$23,LYIndex,0). This gets the range I want i.e. 'Food Last Year'!$C6:$N6 where 'Food Last Year' is the name of the sheet where the data is kept

However rather than using the indirect in the sumif formula I would like to nest the ="'Food Last Year'!$C" & MATCH(B$23,LYIndex,0) & ":$N" & MATCH(B$23,LYIndex,0)in the formula eg =SUMIF(Month,"<"&MonthNumber,"'Food Last Year'!$C" & MATCH(B$23,LYIndex,0) & ":$N" & MATCH(B$23,LYIndex,0)) however excel will not accept any of many the variations I have tried including the example above
It imagine it will have something to do with ' or " or ""
I am pulling my hair out. Please help

 

[GlennUK]

You've left the INDIRECT out. Put the INDIRECT in where you had it before, then complete the nesting.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Hi,

The INDIRECT function converts reference that you are concatenating as a STRING into a real reference.

Otherwise, it's just a STRING.

Skip,

_[red][/red]

 

[GPnew]

Thanks Glenn

But I am trying to get rid of the indirect ....and use the concatenation that is refered to by the indirect in the sumif formula ie "'Food Last Year'!$C" & MATCH(B$23,LYIndex,0) & ":$N" & MATCH(B$23,LYIndex,0)

Cheers

 

[KenWright]

You can't. The concatenation just gets you a string, NOT a reference, so you need to leave the INDIRECT in your formula as well. All that you will be able to achieve doing it this way is the elimination of cell V23 as an intermediary cell holding the concatenation.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[GPnew]

Doh ....I get it now

Thank you all for the advice