Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Elliot J
 
Posts: n/a
Default SQL - Auto Refresh Upon Cell Value Change

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

  #2   Report Post  
Elliot J
 
Posts: n/a
Default

Anyone?

Surely the answer's not *that* obvious?!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I change color of active cell in Excel lfletcher Excel Discussion (Misc queries) 4 April 4th 05 06:29 PM
background color of my cell does not change Colorblinded Excel Discussion (Misc queries) 2 March 27th 05 04:55 PM
Set default in auto fill options, I always want to copy cell, som. OSHAman Excel Discussion (Misc queries) 2 March 26th 05 12:22 AM
Cell color change in formula Layth Excel Worksheet Functions 2 February 22nd 05 07:54 PM
Can an excel cell automatically change fill colors based on values John Clark Excel Discussion (Misc queries) 1 February 5th 05 06:21 PM


All times are GMT +1. The time now is 12:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"