Indirect with Sum / Index - Very Tough

**Excel"*"** · 08-13-2012, 01:47 PM

Hi All,

I'm trying to Indirectly reference a sheetname within a dropdown box along with using some index match information.

My end goal is to be able to change the drop down list that has the indirect reference and the formula will work.

I've attached a copy of what I'm working on! Any help would be AWESOME!!!

The format needs to stay the same.

Thank you in advance. Stay cool

**jason.b75** · 08-13-2012, 02:06 PM

Something like

=SUM(OFFSET(INDIRECT(""&H1&"!D12"),,,1,MATCH('New Sales Ops Report'!K1,INDIRECT(""&H1&"!D1:O1"),0)-1))

**Excel"*"** · 08-13-2012, 02:21 PM

That works awesome, thanks so much. Why the offset function?

**jason.b75** · 08-13-2012, 02:29 PM

You can define the size or the range with offset so it makes the formula easier to write, trying to pass the same range to index by using indirect would get very messy.

Basic construction of it would be =sum(indirect(range):index(indirect(range),match(criteria,indirect(range),0))) that means at least one extra indirect function, and that might not even work, I haven't tested it to see.

You could possibly omit the offset function by use of r1c1 reference style, but that i prone to error with changes to sheet layouts, but that goes for most indirect formulas.

=SUM(INDIRECT(""&H1&"!R12C4:R12C"&MATCH('New Sales Ops Report'!K1,INDIRECT(""&H1&"!D1:O1"),0)+2,0))

**daddylonglegs** · 08-13-2012, 02:37 PM

Another way would be to use SUMIF, e.g.

=SUMIF(INDIRECT("'"&H1&"'!D1:O1"),"<"&K1,INDIRECT("'"&H1&"'!D12:O12"))

[Note that gives you zero if K1 is Jan 1st]