Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 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