I have a Challenge for you Excel Gurus

[AdamGoz]

Hey everyone,

I think this may be possible to do in Excel, but not sure. For you Excel gurus out there, here's the challenge:

On the main sheet of a workbook, there is a list of contacts (in column A, cells A2 onwards. A1 is the heading of the column titled "Contacts"). In columns B onwards, there are various titles such as "phone number", "address", etc.

Each contact has all of their info listed in a single row (For example, Billy J. takes up Row 2, Frank D. takes up Row 3 and so on). Sometimes their information needs to be updated, such as their address. This is simple to do, but here's the difficult part:

It's important to know how old each piece of data on the Main Sheet is. For example. if their phone number has been updated within the last 30 days, the text (or something similar) needs to be displayed as green, if it has been updated over 30 days ago but within 90 days, the text needs to be displayed as yellow, and anything that hasn't been updated within the past 90 days, the text needs to be displayed as red. Each cell needs to be green, yellow, or red, based upon when it was updated, independent of when other cells were updated.

You may use additional sheets that store data such as time/date stamps, calculations in regards to the date, etc. You may use conditional formatting such as Icon Sets if they apply. You may write VBA code.

This is my challenge to you guys, please let me know if you have any questions / need clarification.

 

[Peter_SSs]

Reading this sounds like a homework assignment. If so, you really should have a go yourself and then if you need help with some small part of it, post the details of what you have tried and what extra help you need.
My hint is that I certainly would be using each of these ideas:

You may use additional sheets that store data such as time/date stamps, .... You may use conditional formatting ..... You may write VBA code.

 

[AdamGoz]

Reading this sounds like a homework assignment. If so, you really should have a go yourself and then if you need help with some small part of it, post the details of what you have tried and what extra help you need.
My hint is that I certainly would be using each of these ideas:

I have been trying to figure this out literally all day and can't (although I made some progress and learned new things thanks to this forum). There is a practical application to this problem, which is also why I'm here. Here are the key details of what I've tried (note I'm not good with VBA)...

1) I tried figuring out how to plug in VBA code so that a cell automatically updates its time stamp whenever another cell is altered. The below code shows how to do that for when you change any other cell within a row:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("L" & Target.Row).Value = Date
End Sub

However, this doesn't really help since it only shows when "something" in that row was edited, and doesn't necessarily mean all of the information is up to date or even remotely current. I also tried to change the "L" to a named range of cells, but that broke my code, and I couldn't get further than that.

Here is another article that I feel has VBA code that could help, but I can't figure out how to apply it to my specific problem that is significantly more complicated: https://www.extendoffice.com/documents/excel/1895-excel-record-date-and-time-when-cell-changes.html

2) I tried messing around with conditional formatting and made some progress to a part of this problem. However, it may be too limiting. First, I used this formula in cell A1 on a 2nd sheet, for sake of keeping my Main Sheet clean:

=IF('Main Sheet'!A1<>"",NOW(),"")

So whenever anything is changed in the entire Main Sheet, this cell in my 2nd sheet will return the current date. I formatted this cell to be in the form of a NUMBER. Then, also in my 2nd sheet I input example dates to test out the conditional formatting (as I still don't know how to get the exact date returned for when each cell was last updated), and added an ICON SET rule that applies to the range: Sheet2!$B$2:$E10 (these cells in Sheet 2 mirror the cells containing all of the information in the Main Sheet). I set the rule to return a green dot when the value returned is >= the formula =$A$1-30, a yellow dot when the value returned is >= the formula =$A$1-90, and a red dot for all other instances. This allowed me to get the example dates to return a correct green, yellow, and red dot. However, I couldn't figure out how to then make those dots appear on the information cells on the Main Sheet (I imagine a certain IF function may work to mirror the dots in those cells).


I believe that's all the info I have as to how far I've been able to get. I'm relying on an Excel guru to help solve this one :/

 

[Peter_SSs]

OK, that's some good research, so I'll push you forward some more.

If you don't already have one, insert a new worksheet into the workbook (a copy of your workbook). I've called mine 'DateStamp'. This sheet will be used to record when any cell in the main sheet is altered, or even re-affirmed.
To do that we will use a Worksheet_Change code as you had discovered. You would right click the main sheet's tab and choose 'View Code'.
Paste the code below into the main right hand pane that opens after the last step.

Private Sub Worksheet_Change(ByVal Target As Range)
  Sheets("DateStamp").Range(Target.Address).Value = Date
End Sub

Now, whenever a cell or range of cells in the main sheet is changed the exact same cell or range of cells in the DateStamp sheet will have the current date entered.

In the main sheet, select from cell A1 to end of the data, or wherever the data might extend to in the future and apply
Conditional Formatting -> new rule.. -> Use a formula to determine which cells to format -> Formula: =TODAY()-DateStamp!A1<30 -> Format... and set the green formatting you want** -> OK -> OK

** I would suggest using background cell colours rather than font colours. The reason is that if a cell in the main sheet is cleared, you will still get an indication of how long ago that happened.


You then have to make up two other CF formula rules for the other two colours, get your rules in the appropriate order etc. The red rule may prove a little trickier that green or yellow but I'll leave you to have ago at that yourself.

You can test your CF by entering values in several cells in the main sheet but then going in to the DateStamp sheet and manually changing some of the dates back to, say, 60 daya ago or 6 months ago etc. then coming back to the main sheet to see any CF effects of that change.

Again, post back with particular issue details if you still have problems

 

[AdamGoz]

Thank you for the help. I'm making progress and feel I should be able to figure most, if not all of this out. I'll keep you updated with progress.

One question about a problem I've been running into: When I put VBA code into Excel (I'm using v 2011 for Mac), which I do by opening the Developer Editor, then paste the code into the right Sheet under my current VBA Project, it works at first. Then if I close the code window (where I copy & pasted the code), and go back to editing my sheet, I can only type in 1 character into a cell. The code still works, but I can't type more than just 1 character.

Why is this happening?

Thanks again,
Adam

 

[AdamGoz]

Btw, I figured out the conditional formatting for Green, Yellow and Red. I used the Classic New Formatting Rule, using a formula to determine outcome. Here are the formulas I used:

Green: =TODAY()-DateStamp!A1<30
Yellow: =AND(TODAY()-DateStamp!A1>=30,TODAY()-DateStamp!A1<=90)
Red: =TODAY()-DateStamp!A1>90

Thanks for the nudge in the right direction.

I still need to figure out (feel free to nudge in right direction as I work on this):
1) How to solve VBA issue (detailed in my previous reply).
2) How to make this only apply to certain cell ranges (as it messed up when I tried making the conditional formatting only apply to a section such as B2:C10). I think this is because the formulas above use DateStamp!A1 instead of specifying the corresponding range?)

Thanks!
Adam

 

[AdamGoz]

Btw, I figured out the conditional formatting for Green, Yellow and Red. I used the Classic New Formatting Rule, using a formula to determine outcome. Here are the formulas I used:

Green: =TODAY()-DateStamp!A1<30
Yellow: =AND(TODAY()-DateStamp!A1>=30,TODAY()-DateStamp!A1<=90)
Red: =TODAY()-DateStamp!A1>90

Thanks for the nudge in the right direction.

I still need to figure out (feel free to nudge in right direction as I work on this):
1) How to solve VBA issue (detailed in my previous reply).
2) How to make this only apply to certain cell ranges (as it messed up when I tried making the conditional formatting only apply to a section such as B2:C10). I think this is because the formulas above use DateStamp!A1 instead of specifying the corresponding range?)

Thanks!
Adam

Ok, So more updates here:

• for some reason my issue with 1) seems to have resolved itself. I'm still not sure why that problem ever happened to begin with
• I figured out issue 2). In the conditional formatting main window to manage rules, I changed which cells the rules apply to. I tried this: 'Main Sheet'!$B$2:$C$10,'Main Sheet'!$E$2:$E$10 for each of the 3 rules I'd created (for green, yellow, and red), and it worked! Other cells did not change color when changed, and only the cells that I wanted to show when they were updated were affected.

Thanks so much for your help, and if you have any comment on why issue 1) sometimes happens, please let me know.

Best,
Adam

 

[Peter_SSs]

Glad you worked out the CF.

I don't know about Excel for Mac, so I'll not be much more use I'm afraid. Since the vast majority of members of the forum use PC, you should always point out in your first post that you use a Mac. That way those that have skills in that area can know to assist.