Find only formula result which has updated

neb255

Board Regular
Joined
Sep 14, 2011
Messages
60
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 :biggrin:
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Saagar

Board Regular
Joined
Aug 24, 2011
Messages
205
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,919
Messages
5,447,280
Members
405,446
Latest member
morsecode

This Week's Hot Topics

Top