• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

Excel - conditional updating based on date


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?
Lee Savidge
Lee Savidge
  • 3
  • 2
1 Solution
Rob HensonFinance AnalystCommented:
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
Lee SavidgeAuthor Commented:
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.
Rob HensonFinance AnalystCommented:
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
Lee SavidgeAuthor Commented:
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

Lee SavidgeAuthor Commented:
This works and doesn things as I need them to be done.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now