Excel Query Parameter Update when Cell Value Changes

Question

I'm using Excel 2013 and I have a sheet that gets data from SQL Server 2012.

The query has 4 parameters and gets their values from cells. The box is checked to Refresh Automatically when cell value changes. I want to avoid using VBA.

Data is returned when correct values are in the cells referenced by the parameters.

The issue is with the cells that are dates. In another cell I create a formula that checks if the date is valid, if it is valid then format the entered value as YYYY-MM-DD else format today's date. On the first change the data is updated, but on subsequent changes the data doesn't update. The cell with the formula is used as the value for the parameter.

Answer 1

The issue seems to be that cells used for query parameters can only contains data or a simple function, no nested functions, for a change event to be recognized. In my case: Cell I4 formats the user entered start date using =TEXT(F4,"yyyy-mm-dd"). If it is a date it is converted to text as SQL expects it else it is the same as the input. Cell J4 contains function for first day of current month =TEXT(TODAY()-DAY(TODAY())+1, "yyyy-mm-dd"). The cell used for the parameter is H4 and it contains =IF(F4=I4, J4, I4). The next row (5) is similar testing the end date and J5 provides the month end date: =TEXT(EOMONTH(TODAY(),0), "yyyy-mm-dd") . Then the parameter cell for the end date contains =IF(F5=I5,J5, I5). My next step is to take the query to a stored procedure for better input validation. Since excel seems to only allow 1 parameter for stored procedures (unless ODBC is used) I will have to concatenate the inputs is a cell like this: =TRIM(CONCATENATE(H2, "■",F3, "■", H4, "■", H5)) . Then split the criteria in the SP. Hope this helps someone.