Running Formula from a concatenated string.

V

vlerden

New Member
Joined
Jun 22, 2017
Messages
17
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]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
You use INDIRECT to get ranges, not entire formulas.
 
Upvote 0
Hi

Welcome to the MrExcel forum.

You could use something like :-
Excel Workbook
BD
16xyAxyB
17ABCAABCB
vlerden
Excel 2007
Cell Formulas
RangeFormula
D16=CONCATENATE("A",INDIRECT(ADDRESS(ROW(),2)),"B")
D17=CONCATENATE("A",INDIRECT(ADDRESS(ROW(),2)),"B")


but you haven't indicated which cell the formula would be located nor whether you would be dragging the formula down or across.

How would you like the addresses to progress from $B15 and C$26?

hth
 
Upvote 0
Thanks for the quick reply and welcome. I ended up having to create all of the formulas with the SUMIF statements and pasting the values into the cells. Then I highlighted the few thousand formulas that weren't being recognized as a formulsa and pressed F2 then enter so many times to have excel now see it as a formula. I was thinking about creating a vbs script (as I can not install addons at work) to help me out with having to do it all the time.
 
Upvote 0
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",A$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",B$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",C$20,"])")[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",A$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",B$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",C$20,"])")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",A$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",B$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",C$20,"])")[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",A$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",B$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",C$20,"])")[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",A$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",B$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",C$20,"])")[/TD]
[/TR]
</tbody>[/TABLE]


This is where I Pasted the values, then highlighted all the new values and repeatedly pressed 'F2' then 'Enter'
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_1])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_2])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_3])[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_1])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_2])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_3])[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_1])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_2])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_3])[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_1])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_2])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_3])[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_1])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_2])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_3])[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]



Table_Data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Stuff_1[/TD]
[TD]Stuff_2[/TD]
[TD]Stuff_3[/TD]
[TD]Stuff_4[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]06/22/2007[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]06/22/2007[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]06/20/2007[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Table_Data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Stuff_1[/TD]
[TD]Stuff_2[/TD]
[TD]Stuff_3[/TD]
[TD]Stuff_4[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]06/22/2007[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]06/22/2007[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]06/20/2007

[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",A$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",B$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",C$20,"])")[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",A$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",B$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",C$20,"])")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",A$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",B$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",C$20,"])")[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",A$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",B$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",C$20,"])")[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",A$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",B$20,"])")[/TD]
[TD]=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",C$20,"])")[/TD]
[/TR]
</tbody>[/TABLE]


This is where I Pasted the values, then highlighted all the new values and repeatedly pressed 'F2' then 'Enter'
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_1])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_2])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_3])[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_1])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_2])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_3])[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_1])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_2])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_3])[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_1])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_2])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_3])[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_1])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_2])[/TD]
[TD]=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_3])[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi

Applying my example to your formula you would get :-
Code: 
=CONCATENATE([COLOR=blue]"=SUMIF(Table_Data[Date],TODAY()-",INDIRECT([COLOR=red]ADDRESS([COLOR=green]ROW([COLOR=purple][/COLOR]),2[/COLOR])[/COLOR]),",Table_Data[",INDIRECT([COLOR=red]ADDRESS([COLOR=green]ROW([COLOR=purple][/COLOR])+11,3[/COLOR])[/COLOR][COLOR=#0000ff]),"[/COLOR]])"[/COLOR])
if the formula was placed in a cell in row 15. Adjust the Row referenced in the ADDRESS parts of the formula should it not be in row 15.

hth
 
Upvote 0
I'm getting a concatenation, but the address row sections are not working. is there a specific location I should be pasting this?
 
Upvote 0
I figured out what you were doing and got it to create the functions in a similar fashion as before. However, using this method I am no longer able to drag the formula over for it to concatenate the new formulas, and they still don't run the SUMIF function automatically.
 
Upvote 0
You should be using INDIRECT, with concatenation, to get the ranges for use within the SUMIF not the entire formula.

Let's say you had E1:E100 in A1 and F1:D200 in A2, this is how you would do a simple SUMIF for those ranges using INDIRECT.

=SUMIF(INDIRECT(A1), ">0", INDIRECT(A2))

That's a very simple example but it should illustrate what I mean.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Looking to replace Indirect function to pull dynamic ranges
Replies
1
Views
511
Cubist
Cubist
W
  • Solved
Combining Filter function with Indirect/Concatenate to look up from another worksheet
Replies
3
Views
1K
Fluff
Fluff
G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
484
GeneBF
G
Z
  • Solved
Conditional Formatting (CF Rule) Using INDIRECT Function
Replies
5
Views
587
Fluff
Fluff
P
  • Locked
  • Question Question
Below formula which works on excel 365 only, but also need to be work on excel 2021...
Replies
1
Views
170
Fluff
Fluff

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top