Data Input

[Rich Stanek]

I need a quick solution to a data input issue. I have a sales forecast where
the "end user" had to type sales projections for the rest of the year. They
input the raw number, instead of the formula with the number. Example 5
instead of =5. Since I am using hyperion essbase it will remove all of the
forecast work unless it recogonizes the cell as a formula.

Question? Does anyone know of a quick way of turning 5 into =5 in the
cells?

Thanks

 

[Duke Carey]

I've had a fair amount of experience with Essbase. There's different ways to
skin this.

I usually have a retreive area off to the right and then formulas to the
left that reference the retreived values. Done this way, the users don't
risk losing their input because the retrieve can be performed only by first
selecting the range with the Essbase row & column headers.

If that's not a viable way to go....
Where are the forecast numbers being entered, relative to the retrieve area?
Are they within it or outside of it? If outside, you can simply select the
retrieve range and then use the Essbase menu retrieve to refresh your data.
Essbase will not affect areas outside the selected range.

If the forecast numbers are within the retrieve area, you'll need a macro to
convert each value to a formula. The question is: Where will the macro
reside? Are you doing this forecasting within a template, or does each user
do an ad hoc spreadsheet?

If it's in a template, you can create the macro in the template and all
users will have access to it. If not a template, you've got something of a
mess on your hands.

At any rate, here's the macro. It requires the user to select the cells
that are to be modified before running the macro

Sub CreateFormulas()
Dim rng As Range

For Each rng In Selection
If WorksheetFunction.IsNumber(rng.Value) Then
If Not rng.HasFormula Then
rng.Formula = "=" & rng.Value
End If
End If
Next

End Sub

For information on installing the code see
Getting Started with Macros and User Defined Functions

http://www.mvps.org/dmcritchie/excel/getstarted.htm