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.

 

[My Aswer Is This]

I would be surprised if all this can occur without using Vba.
But I might be surprised.
If you want to use Vba then provide more specific details.
Like sheet names columns and rows

So lets say "John" enters his name in Range("A1")
And enters "100" in Range "B1"
The script would look through Row(1) for "John" and finds John in column "G"

The script would enter 100 in first empty cell in column G
Todays date in column(A) of same row. Bingo Job is done.
Would something like that work?

Or have a Input Box popup.
John enters John then a comma then 100 and clicks OK and the script runs as shown above.

 

[My Aswer Is This]

Try something like this:
Assuming you have automatically entered todays date in column A
This script looks through row(1) for employee name
The script wants you to enter "John" for example in the InputBox that pops up and then a comma ,
and the the sales number John has for today.
So the user should enter: John,300

This way the script searches for John in row(1) and then enters 300 in that column in the same row with todays date in Column A

Sub Input_Name()
'Modified  4/17/2021  6:42:35 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
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim ans As String
Dim LString As String
Dim LArray() As String
Dim anss As String
Dim ansss As String
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
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 = 1 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

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

 

[mummo]

Try something like this:
Assuming you have automatically entered todays date in column A
This script looks through row(1) for employee name
The script wants you to enter "John" for example in the InputBox that pops up and then a comma ,
and the the sales number John has for today.
So the user should enter: John,300

This way the script searches for John in row(1) and then enters 300 in that column in the same row with todays date in Column A

Sub Input_Name()
'Modified  4/17/2021  6:42:35 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
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim ans As String
Dim LString As String
Dim LArray() As String
Dim anss As String
Dim ansss As String
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
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 = 1 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

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

Hi and thank you for the help.

This solution seems to be close to what I need, but since I already have the dates added in column A for the whole year, it adds the number to the last row. Is there a way to find the correct row with todays date and correct column with the name?

 

[My Aswer Is This]

Try this:

Sub Input_Name()
'Modified  4/19/2021  3:17:41 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
DateLastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set searchrange = Range("A1:A" & DateLastrow).Find(Date)
Lastrow = searchrange.Row
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim ans As String
Dim LString As String
Dim LArray() As String
Dim anss As String
Dim ansss As String
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 = 1 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

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]

Lets use this:
It will popup a message Box if Todays Date cannot be found:

Sub Input_Name()
'Modified  4/19/2021  3:42:00 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
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
Dim ans As String
Dim LString As String
Dim LArray() As String
Dim anss As String
Dim ansss As String
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 = 1 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

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

 

[mummo]

Lets use this:
It will popup a message Box if Todays Date cannot be found:

Sub Input_Name()
'Modified  4/19/2021  3:42:00 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
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
Dim ans As String
Dim LString As String
Dim LArray() As String
Dim anss As String
Dim ansss As String
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 = 1 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

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

Thank you, this does the job nicely!

 

[My Aswer Is This]

Thank you, this does the job nicely!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
If you do a test you will see if user does not enter John,77 for example he will get a message box that will popup. Of if he enters Mary,788 and there is no user named Mary he will get a message Box popup

 

[mummo]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
If you do a test you will see if user does not enter John,77 for example he will get a message box that will popup. Of if he enters Mary,788 and there is no user named Mary he will get a message Box popup

Quick question related to this. If I want the button on Sheet2 and the table is in Sheet1, where in the code do I determine the lookupsheet and updatesheet?

 

[My Aswer Is This]

Quick question related to this. If I want the button on Sheet2 and the table is in Sheet1, where in the code do I determine the lookupsheet and updatesheet?

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?