Dec 05 2020 08:52 PM
I an trying to create a database that has 8 columns of data.
The complicated part is, I want to enter the data on line 1 ONLY ( the first 5 columns of line 1) , and have it add to an ongoing database. So every time I hit "Enter" for example , it would add that new entry to the end of my database.
If I can figure out that, then I want to be able to only put data in cell A1 and A2, (the following 4 columns of data will be a Vlookup of cell A2) and those will be added to the database.
I attached the file, Line 14 is the info I want for my Database records, the only input I want to put in is the yellow 2 cells. After entering the 2 cells I want all the info on line 14 added to the end of my dbase.
I know it can be done, my son was in High performance waterpolo and the spreadsheet waterpolo canada used did this exact thing, except I think they just used 1 cell to do the input, for example 2525D1, where 2525 would be input 1 and D1 would be the other input. they separated it somehow, I think with macro.
I hope I did a good job explaining it. If anyone can send Ideas my way.
thanks. I attached the file incase it was confusing.
Dec 06 2020 04:16 AM
See the attached version (now a .xlsm workbook since it contains VBA code).
Dec 07 2020 09:18 PM
@Hans Vogelaar that is Excellent, That is 99% of what I wanted. I was hoping to do it without the Add record button but that is not the end of the world.
It works exactly as designed for a Computer, however VBA will not run on my Excel on my Cell phone. which is what I had originally intended.
but this is perfect. If only I can figure out how you did it.
Dec 08 2020 04:27 AM
I don't think it's possible to do what you want without VBA code, so it will only work on Windows and MacOS, not on Android or iOS.
The command button runs the following macro when clicked:
Sub AddRecord()
Dim d As Long
Dim m As Long
Dim r As Long
If Range("B14").Value = "" Then
Range("B14").Select
MsgBox "Please enter the bus number!", vbExclamation
Exit Sub
End If
m = Range("C13").End(xlUp).Row - 2
If m = 0 Then
Range("C3").Select
MsgBox "Please enter information in rows 3 and below!", vbExclamation
Exit Sub
End If
d = Val(Range("C14").Value)
If d < 1 Or d > m Then
Range("C14").Select
MsgBox "Please enter a valid day!", vbExclamation
Exit Sub
End If
r = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Range("A" & r).Resize(1, 10).Value = Range("A14").Resize(1, 10).Value
Range("B14:C14").ClearContents
Range("B14").Select
End Sub
You can view the code by activating the Visual Basic Editor (press Alt+F11) and opening Module1 under Modules.
Dec 11 2020 07:11 PM