• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 61
  • Last Modified:

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
0
ExpExchHelp
Asked:
ExpExchHelp
  • 7
  • 4
  • 2
1 Solution
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
ExpExchHelpAuthor 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
 
ExpExchHelpAuthor 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
 
ExpExchHelpAuthor 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
 
ExpExchHelpAuthor Commented:
Most excellent!   Thank you, Martin.
0
 
Martin LissOlder than dirtCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now