Look up DataBase and sorting


Board Regular
Feb 2, 2015
Office Version
  1. 365
  1. Windows
=now() current date and timereported date#1234
12/20/16vehicle typepm datelast pm
installed datereplacement date#times replaceds/np/n

I have been working on this basic data base for quite some time. but now it's getting to the point were their are just to much data to look at with out it being organized a little. i'll try to explain. I have been tracking parts from specific vehicles and would like to dump that data into a template I created to view a lot more simpler. I would like to be able to pull up that vehicle information by typing in it's number and it will pull up and sort out the information for that vehicle for that time. meaning on 12/20/16 vehicle 1234 will have different information then 1/16/16 vehicle 1234. my main sheet is called "DataDump" which I type in manually all the information into each cell for that vehicle and date. "Sheet4" is were my template is I created that I would like to dump all that info into and have it sorted out. here's what I have. mind you I added generic names like part 01 to help see what I been doing.DataDump is as long as from A1 to BH actually(the example at the bottom of this message). so when I have all my info added in my datadump and I go to my Sheet4 and type in the vehicle number in the cell that says [#1234] all those fields need to change to what the Reported Date says under [reported date]. if I change the reported date the fields should change for that specific date for that vehicle. does that make make any since? hope this can actually be created.

vehiclereport datepart01 part01 p/npart01 s/npart01 installed datepart01 replacement datepart02part02
part02 installed datepart02 replacement datepart03part03
part03 s/npart03 installed datepart03 replacement datevehicle typepm datelast pm


Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
this code at the bottom I use on another sheet for looking up and verify information, and it then drops it in appropriate rows and columns. how do I modify this code to work with what I'm trying to do in my first post above.

Option Compare Text 'ignore text case
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long
Dim lastcol As Long
Dim rng As String
Dim x As Long
Dim y As Long
Dim frng1 As String
Dim frng2 As String
cnt = 5 'first data row
lastrow = Sheet2.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lastcol = Sheet2.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
If Target.Address = "$D$1" Then
Sheet1.Range("A5:IV65536") = "" 'clear previous
rng = "A1:A" & lastrow
If IsNumeric(Application.Match(Sheet1.Range("D1"), Sheet2.Range(rng), 0)) Then
For x = 1 To lastrow
If Sheet2.Cells(x, 1) = Sheet1.Range("D1") Then
Sheet1.Cells(cnt, 2) = Sheet2.Cells(x, 2) 'id
Sheet1.Cells(cnt, 3) = Sheet2.Cells(x, 3) 'date
For y = 1 To lastcol
If y >= 4 Then
Sheet1.Cells(cnt, y) = Sheet2.Cells(x, y) 'other elements
End If
Next y
cnt = cnt + 1
End If
Next x
lastrow = Sheet1.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
frng1 = "B4:B" & lastrow
frng2 = "B4:E" & lastrow
Sheet1.Sort.SortFields.Add Key:=Range(frng1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheet1.Sort
.SetRange Range(frng2)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
'add issue count
For x = 5 To lastrow
Sheet1.Cells(x, 1) = x - 4
Next x
MsgBox UCase(Sheet1.Range("D1")) & " No data has been entered for this bus at this current time.", vbCritical, "ALERT"
End If
End If
End Sub
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back