Inputting data to correlate with a date

Question

Hi all,I am trying to learn Excel as I go along so please feel free to tell me if there is a better way to achieve what I am doing.I have a master tab showing dates along the top and names down the left hand side. When I click a name it hyperlinks to that individual's tab where he/she answers several questions. There is a formula to turn all of these answers into a single result, which I would like to display on the master tab to correlate against the individual's name and that day's date.I don't think I can just use =!(name)C4 for example, as that tab will be opened again and different answers given on subsequent days - i need the result to be pushed to the master tab. Is that possible or is there another way that I need to be looking at this?Ideally any solution should be Excel 2010 compatible!Many thanks

brad_yundt · Answer

Here is a macro that will track entries made in C4:C9 in the various worksheets, and will report the fatigue score to the appropriate date for that person on the Master worksheet. Because the code is not putting a formula in the cell, the scores will be retained as time goes on. The code must be installed in ThisWorkbook code pane. Also, you must save the workbook with .xls, .xlsm or .xlsb file extension (.xlsx will remove the code).
&nbsp;
The code is not looking for the green column to enter the data. Instead, it is matching the date from Alan's cell C1 with the dates in Master row 3.
&nbsp;
'Put this code in ThisWorkbook code pane. It won't work at all if installed anywhere else!
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cel As Range, targ As Range
Dim i As Long, j As Long
Select Case Sh.Name
Case "Splash Screen", "Master", "DPGs"      'Ignore these worksheets
Case Else
    Set targ = Sh.Range("C4:C9")      'Watch these cells for changes
    Set targ = Intersect(targ, Target)
    If Not targ Is Nothing Then
        Application.EnableEvents = False
        With Worksheets("Master")
            i = Application.Match(Sh.Name, .Columns(1), 0)
            j = Application.Match(Sh.Range("C1"), .Rows(3), 0)
            .Cells(i, j).Value = Sh.Range("C15").Value
        End With
        Application.EnableEvents = True
    End If
End Select
End Sub

brad yundt · Answer

Dirk_gently25&nbsp;I am guessing that Joe Blow's tab is named Joe Blow. If you want a cell on the master worksheet to show the results for Joe Blow on a certain date, you can use the INDIRECT function to build the reference on the fly. The INDIRECT function lets you build the formula pointing to a cell (or range) as text. INDIRECT then returns the value from that target cell.&nbsp;Let's suppose that you want to return a value from Joe Blow's cell C4 today, D4 tomorrow and E4 the day after that. If Joe Blow's name appears in Master cell A2, you could copy across and down a formula like:=INDIRECT("'" &amp; $A2 &amp; "'!" &amp; CELL("address",C$4)) &amp; ""&nbsp;The formula concatenates an empty string in case Joe Blow's cell C4 is empty. Without the &amp; "" at the end, the formula would return 0 if the target cell were empty. Potentially offsetting that good feature, concatenating an empty string will turn a number into text that looks like that number--so the trick is best to use when you know cell C4 will either contain text or be blank.

dirk_gently25 · Answer

Brad Yundt&nbsp;I think I understand what you mean, but unless I am mistaken I don't think that will help me the way I have the spreadsheet set up. I'll try to use a better example.&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 2 3 4 5 6 7 8&nbsp;AlanBobChrisDavid&nbsp;The way I have it set up, Alan would click on his name today (the 1st of the month), which would hyperlink to a simple list of questions. These questions would result in a single digit overall "score" for that day. That score would populate under 1 just using the "=!AlanC5" for example. This all works fine. The problem comes when tomorrow Alan clicks his name and gets the same set of questions, but potentially resulting in a different overall "score" - how do I get the master document (shown above) to put the new score in box 2 and not change box 1?Hope that all makes sense! I am happy to link what I have created so far if that helps.

twifoo · Answer

I’m so sorry to inform you that no amount or quality of explanation would equal the clarity of a sample file.

dirk_gently25 · Answer

Twifoo&nbsp;Please see attached file. This is an early work in progress to show the current problem. All I would like to demonstrate is Alan logging in today and filling out his information, this should be saved under the correct date in the master tab. When Alan logs in again tomorrow, the data should be saved under the new date but the old data/date should not be changed.&nbsp;

Forum Discussion

Inputting data to correlate with a date

6 Replies

Resources