Indirect with sumproduct

[pauldaddyadams]

Hi all

I need some help with a sumproduct and indirect formula please...

I have three variables which I need the formula to take into account.

• Branch, in cell C3 of the “master” sheet the user will select the branch. Here I will need the formula to use the indirect feature so it knows what sheet to lookup.
• Enter Month: Here the user will enter the month
• Month or YTD – this will be used for the sumproduct part of the formula. If month is selected I would like it to return only the current months data however if YTD is selected I would need the month and all prior month months returned.

Can anyone help with the formula I need please? The formula is to go in the red cells.

I have attached an example

[newdoverman]

This looks like it could be a "growing" thing. Is that correct?

If this is going to grow, how are you planning on adding data for future dates?

[pauldaddyadams]

It wouldnt be growing - it would only be for a fixed period of time (a year)

[pauldaddyadams]

Ok - So I have managed to find a solution to one of the problems. This uses an IF formula to determine weather to use index/match or sumproduct:

Please Login or Register  to view this content.

Is this the best way to do it?

I need some help adding in the indirect formula - how do I reference cell C3 rather than "Branch1"?

[FDibbins]

Try this...
actual...
=INDEX(INDIRECT(C3&"!$R$8:$AE$37"),MATCH($B9,INDIRECT(C3&"!$B$8:$B$37"),0),MATCH(IF($C$5="YTD","YTD",$C$4),INDIRECT(C3&"!$R$8:$AE$8"),0))
Budget...
=INDEX(INDIRECT(C3&"!$c$8:$p$37"),MATCH($B9,INDIRECT(C3&"!$B$8:$B$37"),0),MATCH(IF($C$5="YTD","YTD",$C$4),INDIRECT(C3&"!$c$8:$p$8"),0))

[pauldaddyadams]

Hi FDibbins - your formula works perfectly however my data is not correct...

Where you have it looking up the "YTD" column in theory it is fine however my data does not have that column (despite it being shown on the spreadsheet). My data is a direct dump from a P&L however currently it does not have the YTD column - it is a feature I am trying to add.

Until it is added I need to try and use sumproduct as if the column didnt exist.

Can I add indirect to this (or a similar formula):

Please Login or Register  to view this content.

To avoid confusion I have deleted that column on my model

[FDibbins]

OK try these 2 then.

Actual...
=IF($C$5="YTD",SUM(INDIRECT($C$3&"$R9:$AC9")),INDEX(INDIRECT($C$3&"!$R$8:$AE$37"),MATCH($B9,INDIRECT($C$3&"!$B$8:$B$37"),0),MATCH($C$4,INDIRECT($C$3&"!$R$8:$AE$8"),0)))
Budget...
=IF($C$5="YTD",SUM(INDIRECT($C$3&"!$C9:$O9")),INDEX(INDIRECT($C$3&"!$c$8:$o$37"),MATCH($B9,INDIRECT($C$3&"!$B$8:$B$37"),0),MATCH($C$4,INDIRECT($C$3&"!$c$8:$o$8"),0)))

[Bob Phillips]

It's a bit more complex I think, but some of it can be simplified with these formulae

=SUM(INDEX(INDIRECT($C$3&"!$R$9:$AE$37"),ROW(A1),IF($C$5="YTD",1,MONTH($C$4))):INDEX(INDIRECT($C$3&"!$R$9:$AE$37"),ROW(A1),MONTH($C$4)))

and

=SUM(INDEX(INDIRECT($C$3&"!$C$9:$O$37"),ROW(A1),IF($C$5="YTD",1,MONTH($C$4))):INDEX(INDIRECT($C$3&"!$C$9:$O$37"),ROW(A1),MONTH($C$4)))

[pauldaddyadams]

Thanks FDibbins and Bob - both formulas worked brilliantly!

[Bob Phillips]

Really? Seeing as one of FDibbins had a missing ! (okay you might have spotted and fixed that), and that we get completely different answers for YTD, I can't see how both can work brilliantly. For Apr-13 YTD I get 198 and 196, with FDibbins' formula, I see 581 and 613.