Solved: Data population based on a field entry

[Gabba]

Hi,
i've a raw data consists of scores of 15 parameters. The raw data has the following fields: Name, Date, Month and other 15 parameters.
Name is not unique - Same name can be repeated.

Requirement:
When i enter the verifier name, all the details available in the raw data against the verifier should be populated.
Please refer the attachment for a sample.

Please help..

[nst1107]

Paste this code into the declarations of Sheet1(Form) and type a name into your input cell.[vba]Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
'To prevent unnecessarily running this sub further, check which cell was changed.
If Target.Address <> Cells(2, 2).Address Then Exit Sub
'Declare all the variables in one place.
Dim c As Range
Dim i As Long, ofset As Long
Dim records As New Collection
On Error GoTo exitSub
'Make sure the name is valid and fill a collection of addresses.
With Sheets("RawData")
For Each c In .Range(.Cells(2, 1), .Cells(.Cells(2, 1).End(xlDown).Row, 1))
If c = Target Then records.Add c.Address
Next
End With
If records.Count = 0 Then MsgBox "No records found for '" & Target & "'.": GoTo exitSub
'Clear the sheet.
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("B4:B20").ClearContents
If Cells(3, 3) <> vbNullString Then Range(Cells(3, 3), Cells(20, Cells(3, 2).End(xlToRight).Column)).Delete (xlShiftToLeft)
'Format the necessary cells and populate the sheet.
Range("B3:B20").Copy
For i = 1 To records.Count
Paste (Cells(3, 1 + i))
Cells(3, 1 + i) = "Data" & i
For ofset = 1 To 17
Cells(3, 1 + i).Offset(ofset) = Sheets("RawData").Range(records(i)).Offset(, ofset)
Next
Next
exitSub:
If Err.Number <> 0 Then Debug.Print "Error " & Err.Number
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.CutCopyMode = False
End Sub
[/vba]

[Gabba]

Hi,
This works good - I need the below data to be shown in the sheet (Overall count) based on the input given in the fields From date, To date. PFA.

Overall no. of calls checked by the supervisor = 15

Overall no. of callers checked by the supervisor = 5
(Arun, Hari, Raj, Shan, Vijay)

Thanks for your help.

[nst1107]

Here is your workbook with the necessary code added. Hope it helps.

[Gabba]

Hi,
This is great.
Final request: In the sheet "Form", Data1 to Data8 label (column headings B - I) gets changed when the user gives the data in "Verifier name" field.

I want the formatting to be retained - (say name "Raj" entered - 3 grid details filled - Data1 to Data 3; i want Data4 to Data8 to be retained with the same column width)

Row4 to Row16 (Date to Avg rating) data in the grid has to be right-center aligned.
Row17 to Rwo21 (Comment1 to Comment5) data in the grid have to left-center aligned, wrap text.
PFA for your ref. Thx

[nst1107]

No need to code this. Just select all cells, size the columns the way you want them, format rows, etc.. When the sub deletes columns, it shifts to the left, so if all the columns to the right of the deleted columns are formatted the way you want them, you will be alright. Excel will create new columns at the far right of the worksheet, and they will be formatted the same as the columns that used to be at the far right.

[Gabba]

Hi,
Thanks for your help.

[Gabba]

Hi,
When we enter a verifier name that is not given in the rawdata, a msg box "No records found for_____ appears" but the previous data are still shown in the grids.
Ex: Shan entered in verifier name and data shown in the grid. Now i enter Victor which is not given in the raw data. Msg box appears but the previous data for Shan is still shown in the grid. This has to be deleted when no record found is displayed.

Also want to know what the below code does:

[VBA]
For Each c In .Range(.Cells(2, 1), .Cells(.Cells(2, 1).End(xlDown).Row, 1))
If c = Target Then records.Add c.Address
[/VBA]
[VBA]
For i = 1 To records.Count
Paste (Cells(3, 1 + i))
Cells(3, 1 + i) = "Data" & i
For ofset = 1 To 17
Cells(3, 1 + i).Offset(ofset) = Sheets("RawData").Range(records(i)).Offset(, ofset)
Next
[/VBA]

[nst1107]

This has to be deleted when no record found is displayed.

Ok, then, move the statement to after the sheet has been cleared.[vba] 'Clear the sheet.
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("B4:B20").ClearContents
If Cells(3, 3) <> vbNullString Then Range(Cells(3, 3), Cells(20, Cells(3, 2).End(xlToRight).Column)).Delete (xlShiftToLeft)
If records.Count = 0 Then MsgBox "No records found for '" & Target & "'.": GoTo exitSub '<--Here's a better place for this line.[/vba]

Also want to know what the below code does:[vba]For Each c In .Range(.Cells(2, 1), .Cells(.Cells(2, 1).End(xlDown).Row, 1))
If c = Target Then records.Add c.Address[/vba]

This loops through all the names on Sheet3 ("RawData") and fills a collection of the addresses of all the cells that have the correct name.

Also want to know what the below code does:
...[vba]For i = 1 To records.Count

Paste (Cells(3, 1 + i))
Cells(3, 1 + i) = "Data" & i
For ofset = 1 To 17
Cells(3, 1 + i).Offset(ofset) = Sheets("RawData").Range(records(i)).Offset(, ofset)

Next
[/vba]

This loops through all the items in the collection, records, makes a new set of formatted cells (copied from Range("B3:B20")) to accept the following data, puts a header at the top of the column ("Data" + the current iteration of i), and goes down the rows of the column, filling them with data from your sheet, "RawData".