Me*

Help a brother out!
I have a register of all the inspection and test equipment that my Quality Control crew in the field have in their procession. The register shows (along with a bunch of other random crap) the date it was last calibrated, and the date it is next due to be calibrated.
I'm not too bad with excel basics, but am far from a programmer. I've mostly got the sheet working like I want it too. I'm just having a few issues with some minor tweaks I'm trying to do.
Basically, in short, I'm using:
K7-TODAY() to tell me the number of days until the item is due for calibration
Where cell K7 is the date the calibration is due.
I'm using conditional formatting to highlight the cell if the value is less than 20, which is handy, but I wanted more of an action/circumstance than that. For example, a pop-up or warning box.
So I'm also using data validation too, which makes a pop-up if the data in the cell is outside of a specified range. Problem is, that these both work perfectly when you manually enter data into the cell, but if the cell is updated using a formula ie K7-TODAY() the data validation pop-up does not occur (only the conditional formatting)

Has anyone tried to do something similar? Is there a way to make the data validation too work when the cell is updated using a formula?
Or maybe there's a better way to achieve what I'm trying to do.

Come at me!

Cheers

__________________
Signarape!

Not sure if this will work, but try replacing the TODAY function with a reference to cell A1 (or any cell at the top of the page). If you manually type the date in that cell it should make the data validation trigger?

Failing that and without macros, have you considered just using an excel table? That would be easy enough to put filters on to only show ones due for testing, it's no pop up but it's quick and easy

Actually you might need to do the reference to cell A1 (or wherever you put the date) in a DATE formula.

Thanks for your input mate, but I'm still getting the same result.
I'm starting to think that I'm asking too much of the basic data validation tool. I may have to look into the option of using macros to make the sheet behave exactly how i want.
No matter where i put the data, or what cells i reference, data validation doesn't want to warn me about the invalid entry.
Interestingly though, if i click into the cell that I'm trying to attached the warning pop-up to, then click in the formula bar, then click out of it, It gives me the warning I want. It just won't do it when the cell is automatically updated.
It's also frustrating that the warning is only a text box. Ie I can't make the warning reference a cell eg "Item "A1" is due for calibration". It has to be a generic/predefined warning.

ahh well.

__________________
Signarape!

yeah its going to have to be a macro if you really want a popup.

Do you have MS Access? Considering you're keeping a database of info it would work quite well

Google?


KIJO- Tapatalk

__________________
Blue Evo IX- For Sale- You buy, cheap cheap
Hot Pink Civic- SOLD

So far, google has only been slightly more helpful than you.

__________________
Signarape!

Keen for you to share what ever you come up with Matt.

__________________
[SIGPIC][/SIGPIC]

Whats your email addy, can send you a few sheets I have set up and had on site that track days till service for LV's and Calibration etc or other things - that have formulas in that you can change to suit...

__________________
[COLOR=plum]Scoobies for Boobies ![/COLOR][COLOR=magenta]Yay for Pink Bits [/COLOR]
[COLOR=deepskyblue]One Speed, One Gear...Goooooooooooooooo....or six in my case :P[/COLOR]

//estreladesign.com.au
https://www.facebook.com/EstrelaDesign

Thanks Lisa
PM sent.

__________________
Signarape!