• 470K Members
• 5,851 Online
• 568K Conversations

New Contributor

# Inputting data to correlate with a date

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

6 Replies

# Re: Inputting data to correlate with a date

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.

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("'" & \$A2 & "'!" & CELL("address",C\$4)) & ""

The formula concatenates an empty string in case Joe Blow's cell C4 is empty. Without the & "" 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.

# Re: Inputting data to correlate with a date

@Brad Yundt 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.
Date                 1 2 3 4 5 6 7 8

Alan

Bob

Chris

David

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.

# Re: Inputting data to correlate with a date

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

# Re: Inputting data to correlate with a date

@Twifoo 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.

# Re: Inputting data to correlate with a date

To achieve your desired results, you need VBA, which is not my forte. Certainly, the other experts here can help you with that.

# Re: Inputting data to correlate with a date

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).

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.

```'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```
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies