Indirect Function and Substitute in order to create Conditional Drop Down Menus

[XcellGuru]

Hello all,

This is my first post on Mr. Excel.com.

I work for a Machine Shop in the Toledo area. We use a quotation sheet to quote our products to our customers. It has 3 cells that describe the Customer, the Contact person at that customer and finally that Contact's Email Address. I have a drop-down menu in each of these cells. The first, Customer, is based off a simple list of our customers. The Contact drop-down then uses the Indirect Function to search the worksheet for that Customer. The drop down is actually based off of a Range Name saved as that Customer's Name.

However the problem is many of my customers have & or , and since Excel does not allow those symbols or even spaces in a Range Name I have to use Substitute to be able to keep the spaces and the & or ,.

It is important that on the Quote Sheet the Name of the Customer is identical to the actual name of the company so I cant use AND instead of &.

Currently my Second drop down, Contacts, has a formula that looks like this {=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5,"_"," "), "3", "&"), "2",","))}.

My problem is that when I give the Range Name a Name , my drop down doesn't work and Excel tells me that the above formula results in an error.

Please Help

 

[Andrew Poulsom]

Welcome to MrExcel.

Shouldn't you be replacing the invalid characters with valid characters rather than the other way round?

=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5," ","_"),"&","3"),",","2"))

 

[XcellGuru]

Ok so Perhaps I should explain some more

The First sheet in the workbook is the Quote

The Second sheet is the Customer Directory

There are generally 3 or 4 contacts for a Company

A Company name is something similar to J & S Industrial Machine Products, Inc.

So I highlight the 3 or 4 contacts for that Company and Create a NAME in Excel
The name is something like J_3_S_Industrial_Machine_Products2_Inc.

So the first drop-down box shows the Company Name as J & S Industrial Machine Products, Inc. The second drop-down box uses the formula {=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5,"_"," "), "3", "&"), "2",","))} in order to match J_3_S_Industrial_Machine_Products2_Inc. with J & S Industrial Machine Products, Inc.

SO, What Am I Doing Wrong?

 

[Andrew Poulsom]

You have the arguments for SUBSTITUTE the wrong way round:

Excel 2010
	H	I	J
4	Company Name	Your Formula	Suggestion
5	J & S Industrial Machine Products, Inc.	J & S Industrial Machine Products, Inc.	J_3_S_Industrial_Machine_Products2_Inc.
Sheet1

Cell Formulas
Range	Formula
I5	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H5,"_"," "),"3","&"),"2",",")
J5	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5," ","_"),"&","3"),",","2")

 

[XcellGuru]

Thank You. That seems to work alright. We will see how it goes as I continue.

This Is Why I Love Forums! Everyone just helping each other. I am more familiar with Car Forums but as I learn the true Ins-And-Outs of Excel I know this will be a great resource. Excel is just such a powerful tool that comes standard on almost any PC, so many people overlook everything it can do and stick to the basics.

 

[XcellGuru]

Okay, So I got the Drop-down Menus sorted out beautifully.

My next problem is auto-populating the EMAIL cell. I am using VLOOKUP.
My formula looks like this
=VLOOKUP(H7,Customers!B2:B86,3,Customers!$C$2:$C$86)

Cell H7 is the CONTACT cell, with the drop-down menu. Customers! is the sheet titled Customers. On that sheet Collumn A is the company name, Collumn B is Contact at that Company and Collumn C is the Email Addresses.

Im sure that it is something very simple, just as before

Thank you

 

[XcellGuru]

Nevermind, I figured it out. Firstly the Column Index Number was incorrect, and secondly the last bit should only really be TRUE or FALSE. I chose FALSE.