Building a formula from another cell

[zp18]

I have a long irregular list of cells that repeat every 6 lines.
Like this:

Title
blank line
1st shift sales
2nd shift sales
3rd shift sales
blank line

repeat

I can build up a formula like this:
=concatenate("=Sum(B",C14,":B",C15,")")
where C14="3" and C15="5" for a complete formula of =Sum(B3:B5) to
calculate the days sales
now,. how can I get it from a text form to an executable form?

(Incidentally, when I use the concatenate operation shown above, Excel
rips off the last closing parend for some reason.)

Thanks

 

[Jim Cone]

Not sure how you would convert the text representation of a formula.
You can enter the following formula in C6 and fill down.
Assumes Title is in B1 will data repeating underneath it...

=IF(AND(LEN(B6)=0,ISNUMBER(OFFSET(B6,-1,0))),SUM(B3:B5),"")

--
Jim Cone
Portland, Oregon USA
Review of Special Sort add-in: http://www.contextures.com/excel-sort-addin.html

..
..
..

"zp18" <[email protected]>
wrote in message
I have a long irregular list of cells that repeat every 6 lines.
Like this:

Title
blank line
1st shift sales
2nd shift sales
3rd shift sales
blank line

repeat

I can build up a formula like this:
=concatenate("=Sum(B",C14,":B",C15,")")
where C14="3" and C15="5" for a complete formula of =Sum(B3:B5) to
calculate the days sales
now,. how can I get it from a text form to an executable form?

(Incidentally, when I use the concatenate operation shown above, Excel
rips off the last closing parend for some reason.)

Thanks

 

[Jim Cone]

Correction:
"Assumes Title is in B1 will data repeating underneath it..."

Should read...
"Assumes Title is in B1 with data repeating underneath it..."

 

[AB]

Do you mean something like this:
=SUM(INDIRECT("B"&C14):INDIRECT("B"&C15))
?