E
Elliot J
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
)
I am sincerely grateful for any comments or suggestions anyone may
have.
Thanks!
Elliot
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
)I am sincerely grateful for any comments or suggestions anyone may
have.
Thanks!
Elliot