Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Excel - conditional updating based on date

Posted on 2015-02-03
Medium Priority
Last Modified: 2016-02-10

Consider the example attached spreadsheet.

It has 3 sheets:
Main Sheet

Ignore options as it is there just to provide data for the drop down list in the cells 'Main Sheet'$B$2:$B$11

Each week, a user will alter the values in 'Main Sheet'$B$2:$B$11 to one of the options in the drop down list, so Up, Down, Left, or Right. I need that value sent to the corresponding column in the Weekly sheet. Normally I would put in the Weekly sheet something like this: ='Main Sheet'!$B$2 which will display the data from the Main Sheet. But next week, the user will update the Main Sheet and I need this new value fed through to the next blank column on Weekly.

Now, this will not be done via normal data pulling, so I suspect it will be a VBA function on the Main Sheet fields so that each time it changes it updates the corresponding row in Weekly but picks the next blank column. However, if the value doesn't change, how can I trap that? That bit isn't so important because ultimately, I can just instruct the user to change the value and change it back again if need be to force the unchanged value to be fed through. The main question is how to update the Weekly sheet with the new value but put the value in the next blank column?
Question by:Lee Savidge
  • 3
  • 2
LVL 34

Expert Comment

by:Rob Henson
ID: 40586215
How about doing it the other way round? The user populates the relevant value on Weekly sheet and the Main sheet pulls the data from weekly based on a date.

This would then avoid the need for the book to have macros if that is an issue in your workplace.

Rob H
LVL 25

Author Comment

by:Lee Savidge
ID: 40586336
Then there would be no point in having the Main Sheet. The point is, each week the user will go through a series of questions, about 50, and the answer to these depends on certain criteria and that is what gets put on Main Sheet. The idea is, that the Main Sheet is the point where the questions are asked, and Weekly maintains a historical view of the chosen options from Main Sheet.

This is what the end customer wants. My spreadsheet is only an example sheet. The weekly sheet in reality feeds many other sheets which show historical data and charts and these sheets will get large as they are over a year each one. If they update the Weekly sheet, which is yearly in the real spreadsheet, the user is quickly going to be scrolling around a lot.
LVL 34

Expert Comment

by:Rob Henson
ID: 40586588
OK, as you say it would have to be VBA driven but you would have to be very careful on how the user uses the routine to do the Update.

Lets assume the following logic:

1) Weekly sheet is linked to Main sheet by formula as you have suggested.
2) Routine is run BEFORE update is input and copies current week formulas on Weekly sheet to next column and overwrites previous week with values
3) Routine clears cells in Main sheet ready for input

That works fine, but what if User (accidentally) updates Main sheet before running routine, Weekly sheet is now out of sync because current week values were "linked" to previous week on Weekly sheet because the formula hadn't been copied across and values overwritten formula. Routine is run and now Previous and Current week have same values. The routine has been run so cannot undo because running a VBA Routine clears the Undo history. Only (simple) option is to close without saving and start again.

Is the layout of the Main and Weekly sheets the same? Would you be able to literally copy and paste from Main to Weekly, using a VBA routine to find the relevant column before pasting?

If the layout is different, the values can be copied in chunks or individual cells; use the routine to identify the right column and then use that. It wouldn't be physically copy and paste as that is resource intensive in VBA, basically the routine would find the relevant column and then populate the cells of that column.

Rob H
LVL 25

Accepted Solution

Lee Savidge earned 0 total points
ID: 40616102
I did resolve this a different way in the end. In the two sheets, there are corresponding questions where the text is identical and unique. What I did was to run on worksheet_changed event and it checks the text from the column nexxt to the one I'm updating, and then finds the same text on the next sheet. It then moves to the next empty cell on that row and populates it with the value from the first sheet.

This works for what I need. The sheet will be worked on weekly and the users are aware that if they incorrectly set a value they must go to the other sheet and remove it first and then set it again. This is not a commercial thing and is only an internal thing to help the users speed up their work on a particularly long questionnaire spreadsheet.

For reference, the code is:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo WorksheetErrorHandler

  Application.DisplayAlerts = False

  Dim sARText As String
  Dim sRAGStatText As String
  Dim iTargetColumn As Long
  Dim iTargetRow As Long
  Dim wsAMatrix As Worksheet
  Dim sTargetAddress As String

  If Split(Cells(1, Target.Column).Address(True, False), "$")(0) = "I" Then
    sARText = Trim(Target.Offset(0, -6).Text)
    sRAGStatText = Target.Text
    Set wsAMatrix = ThisWorkbook.Worksheets("AMatrix")
    iTargetColumn = wsAMatrix.Cells.Find(What:=sARText, After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlDown, MatchCase:=False, SearchFormat:=False).Column
    iTargetRow = wsAMatrix.Cells.Find(What:=sARText, After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlDown, MatchCase:=False, SearchFormat:=False).Row
    sTargetAddress = wsAMatrix.Cells.Find(What:=sARText, After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlDown, MatchCase:=False, SearchFormat:=False).Address
    wsAMatrix.Range(sTargetAddress).End(xlToRight).Offset(0, 1).Value = sRAGStatText
  End If

  If Not wsAMatrix Is Nothing Then Set wsAMatrix = Nothing
  Application.DisplayAlerts = True
End Sub

Open in new window

LVL 25

Author Closing Comment

by:Lee Savidge
ID: 40624035
This works and doesn things as I need them to be done.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question