Excel Help

[antney74]

I currently use an excel program developed by someone heres before that is not here anymore. This books is a tool for us that has about 20 different sheets for our projects. My problem is with our proposal form on this, I want to be able to enter customers into a new sheet and then I can just use a drop down box on my proposal and it auto fills all the required information (address, contact, phone numbers, etc)

Can anyone help me on this? This current program has a section where you can set up a new contact but it is tied into another spread sheet, it comes up run time error 1004, P:\JobKitCustData\CustomerMaster Mailer Version.xls

 

[DebugGalpin]

Sounds like that address isn't accessible from where you are maybe find the file and copy and paste the file link into the macro to replace the debug error?

 

[antney74]

I never had that sheet, looking how to make a spread sheet that this one can take from.

 

[DebugGalpin]

I currently use an excel program developed by someone heres before that is not here anymore. This books is a tool for us that has about 20 different sheets for our projects. My problem is with our proposal form on this, I want to be able to enter customers into a new sheet and then I can just use a drop down box on my proposal and it auto fills all the required information (address, contact, phone numbers, etc)

Can anyone help me on this? This current program has a section where you can set up a new contact but it is tied into another spread sheet, it comes up run time error 1004, P:\JobKitCustData\CustomerMaster Mailer Version.xls

There are a few ways to do this. The drop down box uses a list function which needs to be in the same sheet. Follow the steps below:-

1) make a dynamic list of customer names

In the Customers Sheet that has the details you require:-

Formula > Name Manager > New

Name: Customers
Refers to: =OFFSET(CustomerSheet!$A$1,0,0,COUNTA(Sheet2!A:A),1)

Then macro:-

Range("Customer").Select
Selection.Copy
Sheets("ProposalSheet").Select
Range("O1").Select
ActiveSheet.Paste

I've used cell O as the basis that this cell isn't populated by any data

2) Create Drop down list

Create new dynamic list

Formula > Name Manager > New

Name: Customer_Name
Refers to: =OFFSET(ProposalSheet!$o$2,0,0,COUNTA(o),1)

then in cell A1:

Data > Validation

Select List
Refers to = "Customer_Name"


Drop down will have the list of customers:

then use a vlookup formula to populate the address, telephone number cells automatically,

e.g. vlookup(A1,CustomerSheet!A:E,2,0)

Obviously if you want you can then use this and produce a macro that will auto populate fields else where as well?