XL 2010: Auto-populating data (pulled from a closed workbook) based on a single entry into a different workbook.

[Excelerometer]

I'm not sure if this can be done, I've been looking into linking workbooks, connections, etc. Any help would be much appreciated. Here's what I want to happen:

Several users complete an excel form (Form) where only certain cells are unlocked for them to enter data. One of these cells is a "Petition #".

A separate excel spreadsheet (MasterList)houses a bunch of data regarding each petition # (names, dobs, hearing dates, etc). This spreadsheet is only used by a select few individuals--we do not want other users opening or having access to this spreadsheet).

What I would like to have happen: When a user enters a petition number in the Form, several fields on that form auto-populate based on data housed in the MasterList--without the user having to open the masterlist.

Is this even possible?

I understand it might be easier to simply protect the masterlist spreadsheet and add the form as an additional sheet, but we need to be able to access and modify this spreadsheet and that would not be possible if there was always someone trying to submit a form.

Thank you for your advice, thoughts, insight,

 

[ChrisM]

Here's how I would do it:

1. In MasterList, make sure the petition data is in a nice clean table format, on a separate sheet with column headers. Give this range a dynamic named range so it expands as you add data.

2. In your user workbook, create a new tab and add a data query to it from ribbon: Data/From Other Sources/From MS Query

3. Point this query to the named range in your masterlist file.

4. Set the query to auto-refresh upon workbook open

5. Use vlookups to pull data from this queried table to your user form

6. Hide the query sheet from the user

The only downside is the data will only be as fresh as when the user last opened the file.

 

[Excelerometer]

Thank you for your help (and apologies for the long delay). I have created the query sheet ("sheet 3" for now). I am having trouble figuring out the vlookup aspect though. First, will this work even with data validations set for a cell? (the user has the option of selecting from a pre-determined list, or if they enter the petition number, that cell will autopopulate.

 

[ChrisM]

I'm a little confused on what you mean. In your first post you said several fields will auto populate based on the petition #, so I suggested using vlookups for this. Are you now saying that sometimes those same fields will auto populate, and other times the user will enter data manually?

If that's the case, then obviously you can't have a vlookup in the same cell. You will have to use VBA to place the lookup data in to each cell every time the petition # changes.

 

[Excelerometer]

Sorry for the confusion. If a petition numbe exists, I want the fields to autopopulate. But sometimes there will be no data in the given fields, which means the user will have to manually enter data.

How would I go about using VBA to place the lookup data into each cell?

 

[ChrisM]

here's an example of how you can do it:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler

Dim rSrc As Range

If Target.Address = "$J$5" Then 'cell where you enter petition number
    Set rSrc = Sheet1.Range("A1:B3") 'set this range equal to your query table
    
    If Application.WorksheetFunction.Match(Target.Value, rSrc.Columns(1), 0) Then
        Sheet1.Range("J7") = Application.WorksheetFunction.VLookup(Target.Value, rSrc, 2, 0) 'J7 is one sample cell to read the data in to
    End If
End If

Exit Sub

errhandler:
Sheet1.Range("J7") = "" 'if no matching petition # is found, clear the cells

End Sub