[Solved] Convert text string to formula

[BWallace]

Background: I have several pages of annual data taken in 5 minute blocks. I would like to generate daily totals. Sum 288 cells in a column sequentially 365 times.
---------------------------------

The data is in column C.

I've put these text characters in five different cells in a row "=sum(C" , "2", .... and merged them into what looks like a formula (=sum(C2:C289) ) but it doesn't function like a formula. Is there a way to make it a functional formula?

---------------------------------
Cell contents examples:

Cell H
Row 5 = =sum(C
Row 6 = =sum(C

Cell I
Row 5 = 2
Row 6 = 290

Cell J
Row 5 = :C
Row 6 = :C

Cell K (Row 5 and 6)
289
577

Cell L
)
)

Cell I merges Cells H.....L
Row 5 = =sum(C2:C289)
Row 6 = =sum(C290:C577)

[RoryOF]

This thread may help
viewtopic.php?f=5&t=62968

[BWallace]

Thanks. The link gives the following:

"=INDIRECT(CONCATENATE("Sheet";B3;".";C3;D3))"

I entered:

=Indirect(Concatenate(H20;I20;J20;K20;L20))

And I got Error 502

I also entered:

=Indirect(Concatenate("Sheet";H20;I20;J20;K20;L20))

And got Error 502

[RoryOF]

Look up ### error message
This is something you need - you must make the effort.

[BWallace]

Error 502 is an invalid argument.

I used an argument that someone else reported to work. On the reply which you suggested might help.

I was hoping someone could tell me why it worked in one case but not in mine.

[BWallace]

Let me state the problem succinctly.


I have a text stream

=sum(C2:C289)

.

I want to turn that text stream into a functional formula.

[acknak]

You can't have a string act as a formula.

You can take the string generated by the formula and follow menu commands to force Calc to take the string as if you had typed it. If the string at that point looks like a formula, Calc will take it as a formula as usual.

First, copy the formulas as text to a new range (otherwise you'll lose the formulas that generate the text)

• Select the cells will the strings that look like formulas.
  Copy to clipboard.
  Click at the first cell (top left) of the destination range.
  Edit > Paste Special ... untick "Paste All", untick all others except for "Text" and OK.

You should now have the formulas as plain text.

Select the copied formula text cells (one column here)
Data > Text to Columns
(keep the default settings)Set the separator to TAB
OK

The text formulas should now be active formulas.

[BWallace]

You should now have the formulas as plain text.

I had the formulas as plain text.

Select the copied formula text cells.
Under Data click on Text to Columns.
Set the separator to TAB. Keep all other defaults.

That did the trick. The text string changed into a functioning formula.

Thank you very much. You just saved me an enormous amount of time.

[BWallace]

Somewhere I'm supposed to mark this "Resolved"?

[acknak]

Click EDIT on your first post in this topic.

Prepend SOLVED to the subject field

Click SUBMIT

[BWallace]

A bit more info. I had trouble getting the text strings converted until I added one more step. After pasting the formulas onto a new page I needed to Right Click -> Default Formatting.

So the sequence runs like this:

1) Paste the column of (text) formulas onto a new page

2) With the entire column still selected Right Click → Default Formatting

3) Format cells to Numbers

4) Data → Text to Columns