Conditional name range

Experts:

I need your help with creating a conditional name range.    Here's what I have thus far.

Cells A1:A10 are saved as name range "Customers".    I then use that name range ("Customer") as a drop-menu in another cell.

Some of the 10 customers (naturally, I have more in reality), however, had not made a purchase yet.   That is, customer in A3 or A6 or A10 have placed 0 orders.   Therefore, I want my drop-down menu to only include the other 7 customers (A1, A2, A4, A5, A7, A8, A9).  Once, any (or all) of the other three customers placed an order, I want the drop-down menu to reflect that change and show up to 10 customers.

My question:   How can I create this dynamic name range (drop-down menu) where I only include customers where value in B1:B10 is greater than 0?

Thank you,
EEH
ExpExchHelpAnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
This workbook dynamically updates the Customer named range which is on sheet 'HaveOrdered'. You can hide that sheet if you like.

Select the yellow cell on Sheet1.
29079091.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You can populate the customers from column A who ordered in another column of the same or another sheet as shown in the attached.
In the attached, you will find that customers who ordered are populated in column J using an Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
Now based on the customers in column J, a dynamic named range is created using the formula given below and used as a list source for the drop down inserted in cell D2.

Dynamic Named Range Formula:
=OFFSET(Sheet1!$J$1,,,COUNTIF(Sheet1!$J:$J,"?*"))

Open in new window

DynamicNamedRange.xlsx
0
Martin LissOlder than dirtCommented:
Neeraj, that doesn't work if a new customer is added in column "A".
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Martin,

We need to update only one formula (formula in column J) based on the number of rows with data in column A and copy it down unless we get blank cells.

The bold row numbers in the following formula in J1 needs to be updated if there are more data in column A.
=IFERROR(INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11>0,ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(J$1:J1))),"")
0
ExpExchHelpAnalystAuthor Commented:
Perfect solution!!!   Thank you.
0
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
ExpExchHelpAnalystAuthor Commented:
Martin:

Again, thank you... your solution is great.   Quick follow-up.    The solution takes all customers and orders in column A and B into consideration.  

Any type of headers seems to throw the VBA off.   Is there any way I could specify the customer range to be from A2:A11 and the order range to be from C2:C11?

Thank you,
EEH
0
Martin LissOlder than dirtCommented:
Are you saying that the order data is in column "C" and not "B"?
0
Martin LissOlder than dirtCommented:
This should fix the heading problem, but it assumes that the data is still in columns A and B. Let me know if I need to change that.
29079091a.xlsm
0
ExpExchHelpAnalystAuthor Commented:
Martin - thank you... appreciate the additional feedback.   In the event I need to insert columns, is it safe to assume I merely have to update all reference from B to, e.g., C (in VBA)?   Or does the code required the columns to be adjacent to each other?
0
Martin LissOlder than dirtCommented:
I've updated the workbook so you can add as many columns as you like between the customers and orders columns. I've tried to make it as flexible as I can but you will need to change lines 12 and 13 in the Worksheet_Change event to match your worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngNewRow As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim rng As Range
Dim rngColumn As Range
Dim strOrdersCol As String
Dim strCustomersCol As String

Const CUSTOMER_HDR = "Customers"
Const ORDERS_HDR = "Orders"
<rest of code omitted>

Open in new window

29079091b.xlsm
0
ExpExchHelpAnalystAuthor Commented:
Most excellent!   Thank you, Martin.
0
Martin LissOlder than dirtCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.