Find only formula result which has updated

[neb255]

Hi,

i am using office 2007 and i need some help with automating a spreadsheet.

my spreadsheet has 2 tabs "Active" and "RegData"

"RegData" is a list of numbers which is updated daily (the previous days data is not stored)

"Active" tracks which numbers are on the RegData tab and then updates a status column via the following formula:
=IF(ISNA(VLOOKUP($M81,RegData!$A:$B,2,FALSE)),"DEGRADED","OPERATIONAL")

i need to be able to quickly find only the numbers which have changed status from Degraded to Operational or visa-versa, and update a 3rd column with todays date if the status changed and if not then i need to leave the date currently in the column. (if we cant change the date like that then highlighting the status which changed is also good).

i would prefer if possible to do this w/o a marco since this is sent to a distribution list and i dont know if they will be able to open a macro enabled spreadsheet. i am a beginner in VBA so if there is no other way then ill take the macro and just save the results.

i appreciate your help

 

[Saagar]

Here is one approach without use of macros.

Let's assume the formula =IF(ISNA(VLOOKUP($M81,RegData!$A:$B,2,FALSE)),"DEGRADED","OPERATIONAL") is in column A in "active" tab.

At the start of each day, before the "RegData" tab is updated with the current day's values, copy & paste-special the previous day's statuses in "active" tab in a new column, say column B. After the "RegData" tab is updated, column A shows current day status, column B shows previous day status and you can put a formula in column C to show which numbers changed status.