Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
1-variable Data Table w/ dependency on MS Query fails to update correctly
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
1-variable Data Table w/ dependency on MS Query fails to update co
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
1-variable Data Table w/ dependency on MS Query fails to updat
Excel
model determines parameters to feed the query, whose results further feed subsequent steps in the Excel model. ******************* This sound like you may be approaching a circular loop. If this is all in one step, the code may be divided into steps with separate command buttons so that excel will "wait" for the results of the query before moving on to the last step. " wrote: Thanks. Wrong on 1, right on 2. No updates of data based on Excel data. Excel model determines parameters to feed the query, whose results further feed subsequent steps in the Excel model. The real issue at hand is (apparently) data tables calculated w/o first waiting for dependent query to generate new results that feed the model. I've noticed some discussion of a similar issue w/ pivot tables, as well as 1 MS KB article which doesn't quite describe my issue but related: http://support.microsoft.com/kb/236990. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
1-variable Data Table w/ dependency on MS Query fails to updat
Well, not really. The sequencing's OK, so no circularity.
Another thing I've discovered is even if I try to do a data table using VBA, I still run into the same problem. There's no awareness that a query needs to finish before proceeding through the VB. This again despite unchecking the background refresh property for the data area. gocush wrote: Excel model determines parameters to feed the query, whose results further feed subsequent steps in the Excel model. ******************* This sound like you may be approaching a circular loop. If this is all in one step, the code may be divided into steps with separate command buttons so that excel will "wait" for the results of the query before moving on to the last step. " wrote: Thanks. Wrong on 1, right on 2. No updates of data based on Excel data. Excel model determines parameters to feed the query, whose results further feed subsequent steps in the Excel model. The real issue at hand is (apparently) data tables calculated w/o first waiting for dependent query to generate new results that feed the model. I've noticed some discussion of a similar issue w/ pivot tables, as well as 1 MS KB article which doesn't quite describe my issue but related: http://support.microsoft.com/kb/236990. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to sort variable data range fails | Excel Discussion (Misc queries) | |||
1-variable Data Table dependent on MS Query fails to update correctly | Excel Discussion (Misc queries) | |||
A Macro that Fails to Perform Correctly. | Excel Programming | |||
use variable as connection in query table? | Excel Programming | |||
Macro Fails to Run Correctly | Excel Programming |