Auto populate a separate form based on data inputted

[DCCC93]

Good Afternoon all,

I was hoping somebody may be able to help with something I have got a little stuck on.

I've got a spreadsheet called 'Jobs' which is 7 columns wide with columns named: Job Number, Date, Name, Address, Telephone Number, Description, Staff Member Attending.

So each Job request that comes in has it's own row which is filled out across columns A to G.
I then have a separate excel document called 'Job Request Form' which at the top has the same 7 headings as mentioned above which I either manually fill in on the computer or print out and then manually handwrite in the entries in the relevant row on the 'Jobs' spreadsheet.

Then the member of staff attending the job fills out further information on the form underneath when the job is completed, action taken, costs etc. (but that is irrelevant for this, just trying to aid your understanding of the process)

What I am trying to do is create it so that when I enter a new job in a row on the Jobs spreadsheet, the Job Request Form automatically fills in the corresponding cells based on what was input for that job number in that row.

I was wondering what my best option was for achieving this?

Any ideas would be gratefully received.

Thanks
DC

 

[dmt32]

Hi,
What about doing the other way around i.e. enter data in in the jobs request form & code sends it to next blank row in Jobs (database) worksheet?

If let me have input ranges for Jobs request Form will post some code for you to try.

Dave

 

[DCCC93]

Hi Dave,

I hadn't thought of that but I guess that could work.
But I arguably need a request form per job number so if I inputted into Job Request Form (effectively the blank master) that would then update the Jobs (database) spreadsheet. However, I then did 'save as' Job Request Form - J1 for example, then would that affect the data in the database table. Or could we set it up so that it takes the info from the form and then stops there i.e. doesn't update with further changes to the Job Request Form spreadsheet.

The cells in Job Request Form are as below:

Job Number: C6
Date: C8
Name: F6
Address: C10
Telephone Number: G8
Description: H12
Staff Member Attending: D12

Hope that helps, and thanks again.

Thanks
DC

 

[oldbrewer]

[TABLE="width: 1238"]
<colgroup><col span="5"><col><col span="8"><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 10"]Job Number, Date, Name, Address, Telephone Number, Description, Staff Member Attending.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]form[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]row 13[/TD]
[TD][/TD]
[TD]jobnum[/TD]
[TD]date[/TD]
[TD]address[/TD]
[TD]tel[/TD]
[TD]descr[/TD]
[TD]who[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]jobnum[/TD]
[TD]date[/TD]
[TD]address[/TD]
[TD]tel[/TD]
[TD]descr[/TD]
[TD]who[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]02/04/2016[/TD]
[TD]addr1[/TD]
[TD]tel1[/TD]
[TD]des1[/TD]
[TD]andy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]05/04/2016[/TD]
[TD]addr4[/TD]
[TD]tel4[/TD]
[TD]des4[/TD]
[TD]dave[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]03/04/2016[/TD]
[TD]addr2[/TD]
[TD]tel2[/TD]
[TD]des2[/TD]
[TD]bill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]04/04/2016[/TD]
[TD]addr3[/TD]
[TD]tel3[/TD]
[TD]des3[/TD]
[TD]colin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]05/04/2016[/TD]
[TD]addr4[/TD]
[TD]tel4[/TD]
[TD]des4[/TD]
[TD]dave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]row 18[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]06/04/2016[/TD]
[TD]addr5[/TD]
[TD]tel5[/TD]
[TD]des5[/TD]
[TD]ed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula giving 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=OFFSET($E$13,MATCH($H$22,$E$14:$E$18,0),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 22[/TD]
[TD][/TD]
[TD="colspan: 2"]enter a job number[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]the form on the right then auto populates[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dmt32]

Hi DC,
Following is a hash of coding for project I assisted with another here but hopefully, will work ok for you.

Starting with the Job Request Form worksheet.
1 – Add an ActiveX CommandButton to the sheet & leave it with its default name “CommandButton1”
1.1 – Exit design mode
2 – right click sheet tab & select View Code from menu & you will be taken to the sheets code page.
2.1 – place ALL following code in the sheets code page:

Dim wsJobs As Worksheet


Private Sub CommandButton1_Click()
    Dim Item As Range, DataEntry As Range
    Dim Data() As Variant
    Dim CellCount As Long, i As Integer
    Dim DataRow As Long
    Dim Action As String
    
    Set wsJobs = Database
    
    Set DataEntry = DataEntryRange(CellCount)


    For Each Item In DataEntry
    If Len(Item.Value) = 0 Then MsgBox "Complete All Fields", 16, "Entry Required": Item.Select: Exit Sub
        'build array
        i = i + 1
        ReDim Preserve Data(1 To i)
        'data values to array
        Data(i) = Item.Value
    Next
    
    'find the next blank row in range
    DataRow = GetRow(wsJobs.Columns(1), Data(1), Action)
    'output array to range
    wsJobs.Cells(DataRow, 1).Resize(1, CellCount).Value = Data
    
    'clear input
    DataEntry.Cells.SpecialCells(xlCellTypeConstants).ClearContents
    'tell user what happened
    MsgBox "Job Number " & Data(1) & Chr(10) & Action, 48, Action

   Set wsJobs = Nothing

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Result As String
    Dim RecordRow As Long
    
    On Error GoTo exitsub
    If Target.Address = "$C$6" Then
        Set wsJobs = Database
        Application.EnableEvents = False
        'check if Job No exists
        RecordRow = GetRow(wsJobs.Columns(1), Target.Value, Result)
        'return existing record to form
        If Result = "Record Updated" Then DatabaseToDataEntry wsJobs.Cells(RecordRow, 1)
        Me.CommandButton1.Caption = IIf(Result = "Record Updated", "Update Record ", "Add Record")
    End If


exitsub:
Application.EnableEvents = True
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

3 From the VBA Editor Menu Select Insert > Module and you will be taken to a new standard module.
3.1 Insert ALL following code in the module:

 'function returns the cell ranges for entry formFunction DataEntryRange(Optional CellCount As Long) As Range
    Set DataEntryRange = ThisWorkbook.Worksheets("Job Request Form").Range("C6,C8,F6,C10,G8,H12,D12")
    CellCount = DataEntryRange.Cells.Count
End Function


Function Database() As Worksheet
    Set Database = ThisWorkbook.Worksheets("Jobs")
End Function


'Function returns either current record row or next row for new record
Function GetRow(ByVal Target As Range, ByVal Search As String, Action As String) As Long
    Dim FoundCell As Range
    'find the next blank row in range
    GetRow = Target.Parent.Cells(Target.Parent.Rows.Count, Target.Column).End(xlUp).Row + 1
    Action = "New Record Added"
    'check if record exists
    Set FoundCell = Target.Find(Search, lookat:=xlWhole, LookIn:=xlValues)
    'if exists - return record row
    If Not FoundCell Is Nothing Then GetRow = FoundCell.Row: Action = "Record Updated"
End Function




Sub DatabaseToDataEntry(ByVal Target As Range)
    Dim R1C1 As Range
    Dim i As Integer
    Dim CellCount As Long
    Dim Data As Variant
    Dim DataEntry As Range
    
    Set DataEntry = DataEntryRange(CellCount)
    'create array from range
    Data = Application.Transpose(Target.Parent.Cells(Target.Row, 1).Resize(1, CellCount).Value)


    i = 1
    With DataEntry.Parent
        'step thru each cell in named range
        For Each R1C1 In DataEntry
            'check if input form range has formula
            If Not .Cells(R1C1.Row, R1C1.Column).HasFormula Then
                'return data to correct cell
                .Cells(R1C1.Row, R1C1.Column).Value = Data(i, 1)
            End If
            'increment to next array element
            i = i + 1
        Next R1C1
    End With
End Sub

When all done, exit the VBA editor and go to the Job Request Form.

4 Enter an existing job number currently in your Jobs database & press enter. If all well, the record for that number should be returned. The caption on the commandbutton should change to show that you are editing an existing record - You can perform an update to the record & when press button, record is returned to database.
5 If you enter a new number, the commandbutton caption displays “Add Record” & when all fields are completed & button pressed, record should be added to new row in the Jobs database.

Hope it works & hope Helpful

Dave

 

[DCCC93]

Hi Dave,

Sorry for the delay in getting back to you, I've been trying to sort this out for myself to get it working exactly as I want but I am struggling.

Firstly, thanks a lot for your help on this and I got the code working perfectly with the following:

On Job Request Form sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Result As String
    Dim RecordRow As Long
    
    On Error GoTo exitsub
    If Target.Address = "$C$6" Then
        Set wsJobs = Database
        Application.EnableEvents = False
        'check if Job No exists
        RecordRow = GetRow(wsJobs.Columns(1), Target.Value, Result)
        'return existing record to form
        If Result = "Record Updated" Then DatabaseToDataEntry wsJobs.Cells(RecordRow, 1)
    End If




exitsub:
Application.EnableEvents = True
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Then on module one exactly as you said:

'function returns the cell ranges for entry formFunction DataEntryRange(Optional CellCount As Long) As Range
Function DataEntryRange(Optional CellCount As Long) As Range
    Set DataEntryRange = ThisWorkbook.Worksheets("Job Request Form").Range("C6,C8,G6,C10,G8,H12,D12")
    CellCount = DataEntryRange.Cells.Count
End Function




Function Database() As Worksheet
    Set Database = ThisWorkbook.Worksheets("Jobs")
End Function




'Function returns either current record row or next row for new record
Function GetRow(ByVal Target As Range, ByVal Search As String, Action As String) As Long
    Dim FoundCell As Range
    'find the next blank row in range
    GetRow = Target.Parent.Cells(Target.Parent.Rows.Count, Target.Column).End(xlUp).Row + 1
    Action = "New Record Added"
    'check if record exists
    Set FoundCell = Target.Find(Search, lookat:=xlWhole, LookIn:=xlValues)
    'if exists - return record row
    If Not FoundCell Is Nothing Then GetRow = FoundCell.Row: Action = "Record Updated"
End Function








Sub DatabaseToDataEntry(ByVal Target As Range)
    Dim R1C1 As Range
    Dim i As Integer
    Dim CellCount As Long
    Dim Data As Variant
    Dim DataEntry As Range
    
    Set DataEntry = DataEntryRange(CellCount)
    'create array from range
    Data = Application.Transpose(Target.Parent.Cells(Target.Row, 1).Resize(1, CellCount).Value)




    i = 1
    With DataEntry.Parent
        'step thru each cell in named range
        For Each R1C1 In DataEntry
            'check if input form range has formula
            If Not .Cells(R1C1.Row, R1C1.Column).HasFormula Then
                'return data to correct cell
                .Cells(R1C1.Row, R1C1.Column).Value = Data(i, 1)
            End If
            'increment to next array element
            i = i + 1
        Next R1C1
    End With
End Sub

That works so that I put a Job Number in C6 and on hitting enter all the other cells autofill with the correct information associated to that Job number which is ideal.

The only issue I have is that I want to have more information on the Jobs sheet than just the information that is pulled through into the jobs request form. I.e. There are 20 columns on my Jobs sheet but I only want data pulling through from columns 1, 2, 3, 4, 5, 12, and 13.

Is there a way in the code that I can link which column each cell in the report form wants to pull the data from and ignore all the other columns that aren't needed?

Thanks
DC

 

[DCCC93]

In short and easy to read version. I suppose what i am saying is the code works great, but is there a way of making the function return data in specific columns rather than returning the whole record row?

Thanks again
DC

 

[dmt32]

In short and easy to read version. I suppose what i am saying is the code works great, but is there a way of making the function return data in specific columns rather than returning the whole record row?

Thanks again
DC

Glad solution worked ok for you.

Functionality was based on my alternative suggestion to your request & range data you provided.
What you are now asking for sounds like two requirements?

1 - to enter all data from input sheet to database
2 - to read limited data from database back to another or same input sheet?

If point 2 correct & you just need this for read only, then you could consider using advanced filtercopy to copy required fields from your database to another sheet.

Would be possible to rewrite code to achieve what you want but don't have too much time at present.

Dave

 

[DCCC93]

Hi Dave,

What I am after is you enter data in all 20 columns in main 'Jobs' sheet.

Then you go into the 'Jobs Request Form' sheet and enter the Job Code which then returns all data associated to that job number row in columns 1, 2, 3, 4, 5, 12 and 13, inserting these bits of data into C6, C8, G6, C10, G8 and H12 (the form) respectively.

Is that possible do you think?

Thanks
DC