From your description, it sounds like you are trying to do 2 things:

1. Have an automatic update of you data (with the FFR changing and updating
the data thru calculated formulas).
2. Run scenarios with the FFR being changed manually so you can compare the
results of the bottom line.

In #1 I think you would have to have the FFR inside the db and the db must
be updated thru external means. Then the MS Query would query the db and
download the recordset to excel on an automatic basis as set in the
properties as you described.

If you want to run scenarios, I think you would want to have the FFR
variable in excel, with a macro to run thru the data table using your
variable as a factor on the data.


" wrote:

I have an Excel financial model whose bottom line values are dependent
on results pulled from a simple MS Query (3 of them, actually, and
together they take about 10 seconds +/- to run).

I'm trying to run a simple data table to see how those results vary by
changing a single assumption.

In my data table, I have a single assumption (Fed Funds Rate) that
should affect the results of the query. This assumption itself is not
a parameter in the queries, however another variable calculated in the
model (that changes w/ FFR) is passed as a parameter.

The problem I'm having?

The query results are NOT changed in the various input variable (Fed
Funds Rate) scenarios I'm trying to depict - the results are held
constant and in so doing the data table is flat out wrong.

I've also confirmed 3 "likely suspects" are set up as they should be:

- Enable automatic refresh is activated on spreadsheet open when
prompted

- The parameter properties are set to "Refresh automatically when cell
value changes"

- Background refresh is NOT enabled in the query data range properties
(this actually doesn't make a difference checked or not)

Anybody have any guidance? Is it just not possible to use these 2
things (MS Query and data sensitivity tables) together?

I suppose I could cook up a macro to do this as a workaround; good
thing I don't have to do it in volume.