Excel - conditional updating based on date

Posted on 2015-02-03
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 33

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 33

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Outlook Free & Paid Tools
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

730 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