Dropdown Box Selection Yields Data Range

[unchumvee]

I work in sales and I'm trying to create a shortcut for my Excel based Product Order Form.

I want to create a dropdown box in A1 that has all of my customer names available (ex. Customer1, Customer 2, etc) chosen from the Range B1:E1. After selecting one of the customer names, I want cell Range A2:J7 below to immediately populate all of this customer's shipping info. I have already entered this shipping date elsewhere on the worksheet and named the Range after the customer (ex, Customer1).

When I used an {=INDIRECT(A1)} array to accomplish this, it works but the formatting is ugly. The array places a 0 into any unused box from the named range. I want the INDIRECT fx to perfectly copy all of the formatted text (including bold/underline/merge&center) from my named range and not place 0s into unused cells.

How can I make this work?

 

[Smitty]

Welcome to the Board!

To return data from a validation list I'd use VLOOKUP.

But you'd need to use VBA to actually return formatting, a function won't do that.

As for not displaying zeros, you can go into Options and turn off "Display zero values".

HTH,

 

[unchumvee]

Thanks, Smitty.

Where can I learn about VBA coding for copying & displaying cell ranges with formatting?

 

[Marcelo Branco]

Maybe this array formula

=IF(INDIRECT(A1)<>"",INDIRECT(A1),"")
Ctrl+Shift+Enter

M.

 

[Smitty]

I'd think you could do it with a Change event since they play very well with validation changes. But if the Product Order Form will be the same and you're just changing customer names/populating cells with that information, then why not just format that sheet the way you want it? Then when the information populates it's not an issue to begin with.

As for the change event, here's some boilerplate code to get you started:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

To start working with it, right-click the sheet tab, select "View Code', paste the code into the new window that opens on the right and follow the comments. And feel free to ask any questions you might have!