Running Formula from a concatenated string.

[vlerden]

This is the string that produces a working formula.

=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$B15,",Table_Data[",C$26,"])")

C26 - is the header of the table
B15 - is the number of days current or before

[TABLE="width: 734"]
<tbody>[TR]
[TD]The result is a formula that I can paste into notepad, then re-copy into excel for the correct result.

=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Column1])


The problem is that I need to run the product of the concatenation in about 1000 cells... I've tried using INDIRECT as my tool but I keep getting #REF! as a result instead of the result from the SUMIF... Below is my INDIRECT

=INDIRECT(CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$B15,",Table_Data[",C$26,"])"))[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[ukmikeb]

Hi

Try :-

=SUMIF(Table_Data[Date],TODAY()-$A1,INDEX(Table_Data,,MATCH(A$20,Table_Data[#Headers],0)))

hth

 

[vlerden]

This worked wonderfully. I obviously have a long way to go. This will really help me learn the INDEX command better. For some reason I never thought about doing that. You really rock dude!!!

 

[ukmikeb]

Thanks for the feedback.

Could you try this :-

=SUMIF(Table_Data[Date],TODAY()-$A1,Table_Data["Stuff_" & COLUMNS($B$1:B$1)])

in cell B1 then copy across and down.

TIA

 

[vlerden]

I like the idea, but the "Stuff_" breaks the formula. I'm consulting master Google, but haven't come up with anything yet