Solved

Excel - conditional updating based on date

Posted on 2015-02-03
5
63 Views
Last Modified: 2016-02-10
Hi,

Consider the example attached spreadsheet.

It has 3 sheets:
Main Sheet
Weekly
Options

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?
Book1.xlsx
0
Comment
Question by:Lee Savidge
  • 3
  • 2
5 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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.

Thanks,
Rob H
0
 
LVL 25

Author Comment

by:Lee Savidge
Comment Utility
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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.

Thanks
Rob H
0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 0 total points
Comment Utility
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

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

Open in new window

0
 
LVL 25

Author Closing Comment

by:Lee Savidge
Comment Utility
This works and doesn things as I need them to be done.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now