Hi Readers,
Could anyone please help with an annoying obstacle I have enountered?
I am using the 'Refresh automatically when cell value changes' option
for MS-Query criteria.
My criteria cell is a date, in the format: 2005-05-31.
I have sussed all the formatting issues, but my problem is this: My
criteria cell is actually the result of a formula used to calculate the
end of the month, from another cell containing the month I'm looking
at.
The criteria cell formula is as follows:
=TEXT(DATE(YEAR(TODAY()),D21+1,0),"YYYY"&"-"&"MM"&"-"&"DD")
The problem is, that when the month cell is updated, my criteria cell
formula then recalculates my new 'end month date' but my SQL report (on
another worksheet) doesn't refresh.
I have found that directly typing in my new 'end month date' forces the
refresh, so I believe the problem is as follows:
The 'auto refresh' function only 'watches' the formula, which of course
remains static, and not the 'value' of the cell which IS changing.
Does anyone know a way around this at all?
I know this could be done using VBA, but I'd much prefer not to employ
its use, if possible, for reasons not worth boring you all with :o)
I am sincerely grateful for any comments or suggestions anyone may
have.
Thanks!
Elliot
Bookmarks