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

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,"])"))

<tbody>
</tbody><colgroup><col></colgroup>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
ABCD
10=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",A$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",B$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",C$20,"])")
21=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",A$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",B$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",C$20,"])")
32=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",A$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",B$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",C$20,"])")
43=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",A$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",B$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",C$20,"])")
54=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",A$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",B$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",C$20,"])")

<tbody>
</tbody>


This is where I Pasted the values, then highlighted all the new values and repeatedly pressed 'F2' then 'Enter'
ABCD
10=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_1])=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_2])=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_3])...
11=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_1])=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_2])=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_3])...
12=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_1])=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_2])=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_3])...
13=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_1])=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_2])=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_3])...
14=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_1])=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_2])=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_3])...

<tbody>
</tbody>



Table_Data
ABCDE
20Stuff_1Stuff_2Stuff_3Stuff_4Date
2112406/22/2007
22106/22/2007
234406/20/2007

<tbody>
</tbody>
 
Upvote 0

Table_Data
ABCDE
20Stuff_1Stuff_2Stuff_3Stuff_4Date
2112406/22/2007
22106/22/2007
234406/20/2007


<tbody>
</tbody>


ABCD
10=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",A$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",B$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A1,",Table_Data[",C$20,"])")
21=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",A$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",B$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A2,",Table_Data[",C$20,"])")
32=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",A$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",B$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A3,",Table_Data[",C$20,"])")
43=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",A$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",B$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A4,",Table_Data[",C$20,"])")
54=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",A$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",B$20,"])")=CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$A5,",Table_Data[",C$20,"])")

<tbody>
</tbody>


This is where I Pasted the values, then highlighted all the new values and repeatedly pressed 'F2' then 'Enter'
ABCD
10=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_1])=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_2])=SUMIF(Table_Data[Date],TODAY()-0,Table_Data[Stuff_3])...
11=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_1])=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_2])=SUMIF(Table_Data[Date],TODAY()-1,Table_Data[Stuff_3])...
12=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_1])=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_2])=SUMIF(Table_Data[Date],TODAY()-2,Table_Data[Stuff_3])...
13=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_1])=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_2])=SUMIF(Table_Data[Date],TODAY()-3,Table_Data[Stuff_3])...
14=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_1])=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_2])=SUMIF(Table_Data[Date],TODAY()-4,Table_Data[Stuff_3])...

<tbody>
</tbody>
 
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

W
  • Solved
Combining Filter function with Indirect/Concatenate to look up from another worksheet
Replies
3
Views
171
Fluff
Fluff
Z
  • Solved
Conditional Formatting (CF Rule) Using INDIRECT Function
Replies
5
Views
281
Fluff
Fluff
J
  • Question
VBA Script to Update Files in a folder, updating Links
Replies
1
Views
108
Micron
M
M
  • Solved
Update formula on mouse click without macro
Replies
3
Views
211
Fluff
Fluff
V
  • Question
Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0
Replies
1
Views
160
kvsrinivasamurthy
K

Forum statistics

Threads
1,214,950
Messages
6,122,436
Members
449,083
Latest member
Ava19

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