Building a formula from another cell

Z

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
 
J

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
 
J

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..."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top