Automatic value insertion to table based on date

[mummo]

Hi,

I'm trying to figure out a way to make daily performance tracking easier. I have a table X with dates, performance (ie. sales) and seller. Example below:

Date	Person A	Person B
14.4.2021	100	200
15.4.2021	150	50
16.4.2021	250	100

Currently each person has to go and manually insert the sales from the day to the correct cell. But I am trying to figure out a way to make it automatic with something like this:

	Person A	Person B
=TODAY()

So the date automatically updates each day and everyone just enters their numbers below their name, and the sum goes to the right row in the master table. I was thinking about doing a macro button with XLOOKUP and copy/paste values only, but is there a way to do it without VBA?
If no, any suggestions better than what I was thinking about are appreciated.

 

[mummo]

This is something new now. Table ? did not know we were working with a Table.
Did not know we were dealing with two sheets.

So please provide more details.

You said Date would be in column A
So date is in column A of what sheet.
And what is the name of the sheet with the names in Row(1)

Please give exact sheet names.

Are we still talking about the same question or is this a entirely a new question?

Sorry, I am trying to be more clear. The data is not in table form.
I have the dates in Column A of "Sheet1" and the names in Row 1 of the same sheet.

I was just thinking that if I want the input button to be in "Sheet2" it would need some altering.
The reason I started thinking about this was user-friendliness, because the worker who inputs the sales doesn't need to see the whole data that is compiled.

 

[My Aswer Is This]

Sorry, I am trying to be more clear. The data is not in table form.
I have the dates in Column A of "Sheet1" and the names in Row 1 of the same sheet.

I was just thinking that if I want the input button to be in "Sheet2" it would need some altering.
The reason I started thinking about this was user-friendliness, because the worker who inputs the sales doesn't need to see the whole data that is compiled.

Ok. I will get back with you.

 

[My Aswer Is This]

Try this:
Will work from any sheet. The sheet name with the Button is not needed.
Script always looks at a sheet name "Sheet1"
If name is wrong then change this line of code:
With Sheets("Sheet1")
If your sheet was named "Alpha"
Your script would need to say:
With Sheets("Alpha")

Sub Input_Name()
'Modified  4/19/2021  8:03:20 AM  EDT
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim Lastrow As Long
Dim col As Long
col = 0
Dim LastColumn As Long
Dim DateLastrow As Long
Dim ans As String
Dim LString As String
Dim LArray() As String
Dim anss As String
Dim ansss As String

With Sheets("Sheet1") ' Your sheet name with data
    DateLastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set SearchRange = .Range("A1:A" & DateLastrow).Find(Date)
    If SearchRange Is Nothing Then MsgBox Date & "  Not Found", , "Oops": Exit Sub
    Lastrow = SearchRange.Row
    LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    ans = InputBox("Enter Your Name  and sales number like this:  John,300")
    LString = ans
    LArray = Split(LString, ",")
    anss = LArray(0)
    ansss = LArray(1)

    For i = 2 To LastColumn
        If .Cells(1, i).Value = anss Then col = Cells(1, i).Column
    Next

    If col = 0 Then MsgBox anss & " Not Found": Exit Sub

    .Cells(Lastrow, col).Value = ansss
End With
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine _
& "Maybe you did not enter your text Properly" & _
vbNewLine & "You entered " & vbNewLine & ans & vbNewLine & "You should have entered: " & vbNewLine & ans & ",300" & _
vbNewLine & "For example", , "Try Again Please"
End Sub

 

[My Aswer Is This]

The sheet name where the button is is not needed.

 

[mummo]

Try this:
Will work from any sheet. The sheet name with the Button is not needed.
Script always looks at a sheet name "Sheet1"
If name is wrong then change this line of code:
With Sheets("Sheet1")
If your sheet was named "Alpha"
Your script would need to say:
With Sheets("Alpha")

Sub Input_Name()
'Modified  4/19/2021  8:03:20 AM  EDT
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim Lastrow As Long
Dim col As Long
col = 0
Dim LastColumn As Long
Dim DateLastrow As Long
Dim ans As String
Dim LString As String
Dim LArray() As String
Dim anss As String
Dim ansss As String

With Sheets("Sheet1") ' Your sheet name with data
    DateLastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set SearchRange = .Range("A1:A" & DateLastrow).Find(Date)
    If SearchRange Is Nothing Then MsgBox Date & "  Not Found", , "Oops": Exit Sub
    Lastrow = SearchRange.Row
    LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    ans = InputBox("Enter Your Name  and sales number like this:  John,300")
    LString = ans
    LArray = Split(LString, ",")
    anss = LArray(0)
    ansss = LArray(1)

    For i = 2 To LastColumn
        If .Cells(1, i).Value = anss Then col = Cells(1, i).Column
    Next

    If col = 0 Then MsgBox anss & " Not Found": Exit Sub

    .Cells(Lastrow, col).Value = ansss
End With
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine _
& "Maybe you did not enter your text Properly" & _
vbNewLine & "You entered " & vbNewLine & ans & vbNewLine & "You should have entered: " & vbNewLine & ans & ",300" & _
vbNewLine & "For example", , "Try Again Please"
End Sub

Thanks again, appreciate the help!

 

[My Aswer Is This]

Thanks again, appreciate the help!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.