Import text from Access to Excel as Formula

[andrewp9]

I am importing data into Excel from Access and using that data in a
pivot table. This sales data is either in US dollars or Canadian
dollars, and I have a cell on my spreadsheet which contains a
conversion value to convert USD to CAD as I want all totals in CAD. I
then have a column in my data range (in excel) which says "If is USD,
multiply sales value by conversion value".

Obviously the number of rows that Excel imports will vary, so I wanted
to add a column in my Access table which contained the formula ( as
text) so that when the information was imported into excel, the
conversion calculation takes place automatically.

When I import the column with the formula into Excel it comes in as
text, and I have to go into each cell and hit enter to get it to
perform the calculation. Is there any way to get around this?

Note: I am pivoting in Excel rather than CrossTab Querying in Access as
I have more than one column I want to pivot by. And I want to perform
the conversion calculation in Excel rather than in Access before the
import because I want the user to be able to change the conversion
value on the fly (i.e. to be able to say, "If the exchange rate was x
rather than y, how would that change our overall sales figures")

Thanks in advance

 

[John Nurick]

Hi Andrew,

I presume the formula is along the lines

=IF(D10="USD",C10*ConversionValue,C10)

where the currency code in column D and the sales value in column C. If
so, I'd think in terms of putting the formula in a column to the right
of the range where the imported data goes, and filling it down all the
cells to the bottom of the sheet, or at least to a row below the last
row your data will ever occupy. If necessary, wrap the formula in an
other IF(...) so it returns a blank in rows where there is no data.

If for presentational purposes you need the calculated column in among
the columns of data, I'd import the data into a contiguous range on one
sheet, and then use formulas on another sheet to display the data values
and calcualted values with the desired layout.

Finally, if for other reasons it's necessary to store formulas in a
database field, I'd use a little Excel macro to convert the imported
"formula as text" into an actual functioning formula. It would probably
only have to be something like this:

Dim C As Range
Dim R As Range

Set R = '[Intersection of the column and the sheet's UsedRange]
For Each C in R.Cells
C.Formula = C.Value
Next