Export Actual Formula to Excel

[Bennie47250]

Using Crystal 7.0

When I discussed this project with my boss, I didn’t think it could be done but now that I have tried it, I now think maybe this can be done after all.

The finished report will be exported out as an Excel spreadsheet (version 5).

There will be a Cell showing the total number of vacation hours available.

There will be Cells where a person will enter the number of hours they would like to take off. Next to this Cell, we would like to show the remaining hours of vacation. This is where we would like to export the actual formula that Excel would use

Excel Sheet will look like this:

Total hours available 160

Hours Requested Hours Remaining
(Excel Column A) (Excel Column B)

In the Hours Requested column I have entered a text object field and have a 0 in it

In the Hours remaining column I have entered (as formulas) are the Excel formulas for example: “= A1-A2” or “= B2-A3” or” = B3–A4”

The formulas, when export to Excel, show up as = A1-A2 in the Excel Cell. However Excel is not recognizing these as formulas. But if I click at the end of the formula (in the formula bar) and press enter or click out of the cell, Excel will recognize it as a formula and perform the calculation.

Any thoughts on how to (1) how to format the formula so Excel will recognize with without any additional actions or (2) a different way to achieve the same end result.

Thanks
Bennie

 

[synapsevampire]

I don't think that this is possible in Crystal, however you might post process the file through an Excel macro/vba to accomplish this.

-k

 

[dgillz]

Why export to excel at all?

Usually peopleexport to excel so they can further manipulate the data once it is there. My approach is to get the desired end result in crystal itself, so there is never a need to export.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[Madawc]

Where I work, business users are mostly more familiar with Excel than Crystal. Everyone has Excel, not everyone has Crystal, because the licences are expensive. They may also wish to merge the data with other data that isn't on the Crystal warehouse. Maybe some of it is too confidential for IT, or it may not be convenient to store.

As for text exported as text, this is probably to protect users from accidentally getting text interpreted as something they didn't intend. It still seems to be the case for Crystal 10.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Bennie47250]

While I was unable to find a way to format the Crystal formula to export to Excel as an Excel Formula, someone on the Office Forum provided an easy way to "format" the column so that Excel recognized them as formulas.

This step in Excel is to select the column and then on the tool bar select Date -> Text to Column.

Like dgillz, I too like to perform as much of the calculations in Crystal as possible. Why make it so someone has to do the calculations every time they receive the report, when it can be done for them one time in the report and then it is always there.

However for this project, we are sending out a vacation schedule at the beginning of their anniversary date and they will enter their requested vacation dates as they want them so this information is not known at the time the report is generated.

 

[synapsevampire]

Why use Crystal?

Excel can query data directly, and I often use this means instead, eliminating the Crystal layer, especially for your requirements.

-k

 

[Bennie47250]

Good Question and a valid point.

We will use our 3rd party Crystal scheduler to run the report the last of each month and e-mail to the folks who's anniversary date is occuring.