Conditional IF to be used for filling in Text

**TheErbstur** · 01-01-2010, 06:35 PM

I use Excel 2007 as way of entering my job quotes for a large number of clients. In order to speed the process of order entry, I would like to be able to simply select a specific customers account number from a drop down list and have the remainder of that customers information propogated to the various cells on the page based on that one specific cell.

I'm using two worksheets for this purpose, the main worksheet contains the quote I am building and the other worksheet is the client database containing all the various information, account number, name, address, phone number etc.

So the formula in WORDS would be something like;

If Cell A2="Acct XYZ", then Cell B2="CompanyName for Acct XYZ" and Cell C2="Phone No. for Acct XYZ" and so on.

I've gotten the formula to this point;

=IF($A$2=Customers!$A$5, Customers!B5) Company Name

=IF($A$2=Customers!$A$5, Customers!C5) Contact Name

=IF($A$2=Customers!$A$5, Customers!D5) Phone Number

and so forth.

But of course this formula only works for the one client who happens to be on line A2 of the Customer worksheet.

I need the formula to be dynamic so that it doesn't matter which Account Number I select from the drop-down list, it will always look to the cells to the right of that specific customer and find the appropriate data. (My Customers Data Information Worksheet is pretty simple, the first cell contains their Account Number and all their additional data is to the immediate right, therefore each line of the worksheet is one customer)

I've attached a small data file with sample data

**protonLeah** · 01-01-2010, 07:29 PM

Maybe:
Sample1(bvj).xlsm

**TheErbstur** · 01-02-2010, 09:27 AM

Ben,
Wow...you did it! Thank you so much!
Having said that, who do I have to kill in order for you to show me HOW you did it?

Thanks to the sample data sheet you returned, I can see that you used (what I believe to be) a Macro.
I know just enough about macro's to know that I don't know nearly enough about them.
Would it be asking too much to have you walk me through, step-by-step the creation of the macro you used?
Thanks Again.

**Palmetto** · 01-02-2010, 10:12 AM

The attached workbook builds on your original approach using Data Validation to select clients and uses a look up formula to match details to the selected client.

Dynamic named ranges were created for the Customer Number, client database, and the database header.

This look up formula is used and copied across the columns and down the rows.

=IF($A2="","",INDEX(Database,MATCH($A2,CustNo.,0),MATCH(B$1,Header,0)))

**protonLeah** · 01-02-2010, 07:08 PM

No macros are used. In your sample workbook, you had a named range of account numbers: CustNo. that referred to the fixed range Customers!$A$5:$A$7

I replaced it with a dynamic named range that will adjust itself with changes on the CUSTOMERS sheet.

Please Login or Register  to view this content.

says, build a list from sheet Customers, starting one row down (A2) and zero columns from cell A1 column A).
The list will have a length one row less than column A, because of the header, and will be one column wide. So the list range will be A2:Ax (x being calculated by Excel). That list is used for your data validation entry pick list.

That is also how I created the dynamic table CustomerDataBase to be used in the VLOOKUP formulas, except that the table is seven columns wide.

Please Login or Register  to view this content.

This formula checks if there is an account number in the same row of column A, if the cell is not empty, it just looks on the Customers sheet for the same account number and goes over to the same column on the customers sheet that the formula is in on the Quote Entry sheet and copies the data. If the account number cell is empty, it leaves the affected cell blank also.