Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA vs Functions
Initially, a worksheet containing a lot of resource info (dates, rates,
hours, etc, etc) was updated manually by one person and I am now slowly trying to add in functions to automate as much as possible. To improve data accuracy. I have a column to identify if a resource is active/inactive and when a resource becomes inactive, several columns require input, such as date left, reason left, etc etc. There is a variety of columns to fill out when a resource leaves. When inactive, I would like to be able to check the impacted columns to see if proper values have been entered. Is there a way to do this? If possible, I would like to notify the person that is entering the data that they forgot to update something. I havent found a way to do this with excel functions, but perhaps in VBA? I am just beginning to learn VBA but dont know enough about it yet. Thanks for any help provided. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA vs Functions
Before you do any coding, you may be able to get by with conditional
formatting to highlight the cells that need inputs. Say a reference (Active, Inactive) cell is C5 and D5 is a cell needing input if the C5 value = "Inactive" then this conditional format formula: =AND($C5="Inactive",D5 = "") along with a cell coloring format will give you and the analyst a visual cue regarding cells that need updating. You can copy the formatting to all of the cells that are tracked. There are all kinds of VBA ways to automate the update input process. Keep the conditional formatting though. SteveM MLK wrote: Initially, a worksheet containing a lot of resource info (dates, rates, hours, etc, etc) was updated manually by one person and I am now slowly trying to add in functions to automate as much as possible. To improve data accuracy. I have a column to identify if a resource is active/inactive and when a resource becomes inactive, several columns require input, such as date left, reason left, etc etc. There is a variety of columns to fill out when a resource leaves. When inactive, I would like to be able to check the impacted columns to see if proper values have been entered. Is there a way to do this? If possible, I would like to notify the person that is entering the data that they forgot to update something. I haven't found a way to do this with excel functions, but perhaps in VBA? I am just beginning to learn VBA but don't know enough about it yet. Thanks for any help provided. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA vs Functions
Thanks, I will give that a shot.
"SteveM" wrote: Before you do any coding, you may be able to get by with conditional formatting to highlight the cells that need inputs. Say a reference (Active, Inactive) cell is C5 and D5 is a cell needing input if the C5 value = "Inactive" then this conditional format formula: =AND($C5="Inactive",D5 = "") along with a cell coloring format will give you and the analyst a visual cue regarding cells that need updating. You can copy the formatting to all of the cells that are tracked. There are all kinds of VBA ways to automate the update input process. Keep the conditional formatting though. SteveM MLK wrote: Initially, a worksheet containing a lot of resource info (dates, rates, hours, etc, etc) was updated manually by one person and I am now slowly trying to add in functions to automate as much as possible. To improve data accuracy. I have a column to identify if a resource is active/inactive and when a resource becomes inactive, several columns require input, such as date left, reason left, etc etc. There is a variety of columns to fill out when a resource leaves. When inactive, I would like to be able to check the impacted columns to see if proper values have been entered. Is there a way to do this? If possible, I would like to notify the person that is entering the data that they forgot to update something. I haven't found a way to do this with excel functions, but perhaps in VBA? I am just beginning to learn VBA but don't know enough about it yet. Thanks for any help provided. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Conversion from Spreadsheet Toolkit functions (ESSV....) to EssBase API functions | Excel Programming | |||
excel functions and User defined functions | Excel Programming |