Choose from list and other cells change accordingly

Ok I have a file with 2 sheets basically. Sheet 1 is basically a database of agents with their addresses, tel, details... With this database I created a table so I can move around easily.

In Sheet 2 I created a standard contract for these agents. So I basically need to create 1 contract per agents and fill in their details.

So I thought of doing it like this:

I create a dynamic range from Sheet 1, and then in Sheet 2 I create a drop down list where I pick the agent name. Now the other cells I would like to be filled in automatically according to the name I choose from the drop down list.

All info about 1 agent is all in one row.

Is there anyone that can help me with this or who could come up with another alternative.

Thanks guys.

Hi,

If you need to produce all the contracts in 1 go, then what you need to do is to have a list somewhere of the 1 piece of information that can be used to derive the rest of the information.

For example - if it was an agent ID, then produce a list of the Agent ID's, and in the contract page, use VLOOKUP to reference the entered ID to obtain the relevant details.

Then write a macro that references the list, and walk through that list 1 at a time, passing the ID to the contract, and then either storing a copy of that contract as a seprate 1 page workbook, or printing it out.

HTH

PS - if you want an example then see below:

NB - You will need to have set the directory referred to below up, otherwise it won't work.

Option Explicit

Sub savethem()
Dim intrwindex As Integer
Dim Live_Book As String
Dim New_Book As String
Dim New_name As String
Dim agent As String
Dim i As String

Live_Book = ActiveWorkbook.Name

Sheets("Statement").Select

For intrwindex = 1 To 35
agent = Worksheets("List").Cells(intrwindex, 1).Value
Worksheets("Statement").Range("C4").Value = agent

    Sheets("Statement").Select
    Sheets("Statement").Activate
    Sheets("Statement").Copy
    New_Book = ActiveWorkbook.Name
       
    Workbooks(Live_Book).Activate
    Sheets("Statement").Select
    Range("F13:J75").Select
    Selection.Copy
   
    Windows(New_Book).Activate
    Range("F13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
    Range("A1").Select

    New_name = "Statement for " & Range("C4").Value & ".xls"

' save the file
        ActiveWorkbook.SaveAs Filename:= _
                              "C:\Agent Statements\" & New_name, FileFormat:= _
                              xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
                            , CreateBackup:=False
ActiveWindow.Close

Next_Unit:
    Live_Book = ActiveWorkbook.Name

Next intrwindex

End Sub

Dear Alan,

Thanks for your quick reply.

I don't actually want them to be produced all in one go, but rather want to be able to print them one-by-one. Some contracts might need some small changes here and there.
Also I am not really familiar with the use of Macro at the moment so if possible would just like to bypass that one.

If basically I could just choose the Agent from a drop down list, and then have fe. cell C1 to be the responding address, E2 the Managers name etc...

There must be a pretty easy way to do this no?

Thanks a lot.

The easiest way is to use a series of vlookup functions in the cells you want populated from the database.  Just go to Insert / Function, type vlookup in the 'Search for a function' box and click the Go button.  Then click the OK button, and a dialog will prompt you through the info you need.  Make sure the 'Range_lookup' value is TRUE in order to make sure you get the information from the row you want instead of from the next row.  You may also need to make sure your database is sorted by the agent name.

A formula that would work involves the index and match functions.

Your data is stored in a table with header titles across the top, and each row below it contains the data for a single Agent. 

Col A              Col B               Col C

AgentName   AgentAddress   AgentMgr

John Doe      123 Main St.     Mary Jones

Janet Brown   456 2nd Ave     Doug Jones

The Agent Name column becomes your list for your drop down list.  This can be set using Data Validation and choosing the List option. However, because your data table is on a different sheet, you need to create a Named Range for it. The most straight forwad way of doing this ins xl2003 or 2007 is to select your column of agent names, and in the upper left hand corner of the window, right above the Column A Header is a white box. Type the name AgentName in that white box and press enter.  Now column A on Sheet one can also be referred to as AgentName in any formulas. You should also create named ranges the same way for AgentAddress and AgentMgr. 

Now go to Sheet 2 and select the cell you want your dropdown list to be in. Goto the Data validation button on your version of Excel.  Choose List, and in Source type =AgentName.

Lets say your dropdown list of Agent names is in Cell A1.

The formula in C1 would be =INDEX(AgentAddress,MATCH($A$1,AgentName,0)). E2 formula would be =INDEX(AgentMgr,MATCH($A$1,AgentName,0))

Additional dynamic functionality could be added using the Offset formula in the Named Ranges. Check the Excel Help for more details about Named Ranges, Offset, Index, Match and Data Validation and their proper uses and full syntax, or let me know if you have any more questions about it.

Curtis

Thanks Curtis,

The thing though is that the database is in another sheet.

And I would definitely need to use the Dynamic Named Range (I know how to create those).

In attachment you can find a simplified version of what I am trying to accomplish. Could you modify and show me how?

Thanks a lot guys.

Dear Curtis,

I saw you changed your reply.

It's working now. Ur a superstar. Exactly what I wanted.

Thanks mate.