SQL Connection in Excel - Filtering on Date based existing Data

[PCRIDE]

HI, I am using a SQL QUery to pull data into a Table, in Excel. Everything work great. However my query is about a 4 month range and I need at least 4 months of data in order for the forecasting to work properly.

http://www.velocityscape.com/help/database_how_to_s/connecting_to_sql_from_excel.htm


Once I have ran the Refresh once, I have all the historical data to do the forecasting, what I need to tell Excel to do is.

Keep the existing data and add new data based on TODAY() date.

Is this possible so I don't have to refresh 4 months of data every time this is refreshed? A 4 month date range can take up to 15 min to run, whereas 1 days of data will take only about 30 seconds.

I simply want to keep the historical and add on each new day of data.

thanks,

 

[PCRIDE]

Bumping this to the top.

After I have converted my manual data dumping into this SQL based query everything works great. I do have to set the Date manually and wait for the entire refresh to take place which is about 5 minutes for 2 sheets to update.

The question I have on this. Now some of the formulas (SUMIFS) formulas are not working. It seems like Excel doesn't want to SUMIF but I have checked each section of the formula. For example, I can create a SUM formula between 2 sheets that now use the data connection and it works, but as soon as I add the SUMIF and add multiple criteria it fails.

Any ideas? I have checked and double checked everything..


Here is my code. Again it will work doing a simple sum between the 2 workbooks, but not the whole formula.

IF(Forecast!$A$60=FALSE,"",(SUMIFS('[-With ID.xlsm]Data'!$J:$J,'[-With ID.xlsm]Data'!$W:$W,B5,'[-With ID.xlsm]Data'!$C:$C,"Aux1",'[-With ID.xlsm]Data'!$AB:$AB,"1")/60)/(SUMIFS('[-With ID.xlsm]ACD Data'!$O:$O,'[-With ID.xlsm]ACD Data'!$B:$B,B5,'[-With ID.xlsm]ACD Data'!$AD:$AD,"1")/60))

 

[PCRIDE]

Excel Data Connection and Formulas

When using excel to connect to SQL, you can configure the connection to dump in data as a pivot table or Table. I have chosen a table. When using Formulas in non Table formatted cells next to the SQL Table dump, the formulas seem to follow the orginal row of data. So each time I refresh the formulas are not pointing to the correct row causing the data to be skewed.

Any ideas on how to solve this?

Here is a picture of Excel Table Properties which might be moving the formulas?
http://sdrv.ms/MmK5XK

Its not a virus - LOL , it linked to my Sky Drive