forcing recalculation of cell contents

P

philh

Hello

I have a tool which generates a report and exports it to an exce
spreadsheet. I would like some of the fields on the spreadsheet to b
functions of others, and I would like the spreadsheet to do th
calculation, not the reporting tool.
(The report populates a 'items processed' column, but the number o
hours spent is not available here, so that field is blank. I want th
'items per hour' field to be a function of the 'items processed' an
'hours' fields.)

So, the report populates cell A1 and leaves B1 blank. I want cell C1 t
be =A1/B1 (I don't mind the division error while B1 is empty). Th
report enters the text "=A1/B1" in the cell, but when I open th
spreadsheet, that is exactly what appears in the cell (the si
characters '=', 'A', etc.). The formula it is not interpretted o
calculated.

Doing a 'calculate now' (F9) doesn't have any effect. I had though
that was by best hope. Any ideas out there? Or am I trying somethin
that just ain't gonna work?

PHi
 
K

kkknie

If the cell is formatted as text, it will work this way (since i
assumes you are entering text rather than a formula). You can eithe
manually format it as General (or number) or use the code:

Range("C1").NumberFormat = "General"
or
Range("C1").NumberFormat = "0.00"
or any other numeric formatting.
 
V

Victim of Office Hand-Me-Downs

well, i'm not sure exactly what you're talking about that
you're doing, but you might try putting another worksheet
in the book with your report sheet
that way (it may be a little extra work) you can formulate
the extra sheet to calculate your report for you
it's a simple worksheet to worksheet (in the same book)
formulation, just use the formulas you see fit to connect
the two sheets
you could even have a third sheet to clean up any mess you
may have made
hope this helps!
 
P

philh

Thank you, kkknie for the very quick response. I had not thought o
trying that and gave it a whirl immediately.

Unfortunately it did not work for me. I think that excel is stil
deciding to interpret that cell as literal text, so the number forma
has no effect.

It seems that excel only decides if the cell content is a literal or
formula when the cell content changes. I need a way of prodding it t
make that decision without changing the cell content.

Ah well, thanks anyway. I appreciate your suggestion.

PHi
 
P

philh

Thanks Victim of Office Hand-Me-Downs for thinking about my problem
I'm sorry that I didn't explain it too clearly.

A lot of my problem stems from the limitation in the tool I am using t
generate the reports. It just uses excel as an option to render th
report (it could do PDF, HTML, etc.). Each report is exported as
single workseet in a single workbook. It can do some cosmeti
formatting (fonts, etc.) and can drop text into cells but that's abou
it. Anything fancier (multiple workbooks, macros, etc.) is beyond i
(the reporting tool, not excel obviously).

Thanks anyway
 
D

Debra Dalgleish

Perhaps formulas are visible. To turn that feature off, choose
Tools>Options. On the View tab, remove the check mark from Formulas (at
the bottom left of the dialog box).

The shortcut key for toggling View Formulas is Ctrl + `
The accent grave (`) character may be at the top left of your keyboard,
just below the Esc key.
 
K

kkknie

Here's a quick macro that will search through the active worksheet and
evaluate anything that looks like a formula.

Sub FixTextFormulae()

Dim r As Range

For Each r In ActiveSheet.UsedRange
If Left(r.Value, 1) = "=" Then r.Value = Evaluate(r.Value)
Next

End Sub

Just paste it in a new module in the workbook and either put it on a
new button or just call it from the Tools | Macros toolbar. If it
needs to be available for multiple workbooks, just add it to your
personal macro workbook.

K

P.S. Post back if you need help with any of this.
 
P

philh

Thanks Debra. Another suggestion that I had not considered
Unfortunately again this didn't seem to be the problem. When I wen
looking the options were already set up the way you were suggestin
(the check box was empty).

Gotta say though, I am very impressed with the responsiveness of thi
newsgroup. Thanks to one and all
 
P

philh

Thanks again kkknie. I'm going to try that macro and, on the assumptio
that it works, I'll use it and that's me problem solved. I'll have t
get to that tomorrow though.

Anyway, thanks very much for the repeated help.

Cheerio

PHi
 
P

philh

Well, the prize (if there was one) goes to Debra.

Following that link you gave, and applying a little lateral thinking,
found a solution.

I do a 'replace all' to replace all occurences of an equals sign wit
an equals sign. This seems to be enough to convince excel to look twic
at the cell and realise that its a formula.

This is quite the craziest thing I have seen in days, but it works, s
thanks Debra in particular and everyone else who contributed
 
Top