Dynamic named range based on two criteria

[Samantha McNeill]

I'm trying to create a dynamic named range based on two criteria in different columns. My data set is A4:AQ5000 in a sheet called "CS". Column C is "Description", column V is called "Family" and column W is called "Product". I want to create a named range called "Smith" of the descriptions in column C where column V is "Smith" and column W is "Accessory".

Please Login or Register  to view this content.

Every row has data but I have purposely tried to show the family and product can be in any row from 4 to 5000.

Thanks in advance.

[kersplash]

Maybe attach a sample workbook with a mock-up of what you expect to see.

[Samantha McNeill]

Attached is an example file. I have created a named range called "Smith" but linked the cells manually meeting the criteria ie Family - Smith and Product - Accessory.
Ideally the named range will be dynamic. On the second worksheet called "Family" is a list of the family names that a named range will need to be created for.

[Glenn Kennedy]

What do you want to DO with these named ranges? What are you trying to achieve as ab end result? I think you may be over-complicating things...

There is no second worksheet.


Also, please amend your profile to give a general indication of your national location - as regional settings often influence the required solution.

[Glenn Kennedy]

I have made a WILD guess as to what you ACTUALLY want. take a look.

[Samantha McNeill]

Thanks for your assistance. I've updated the example hopefully to explain more clearly what I'm wanting to do. In the example file there is two sets of data on the CS sheet. Top set is what I have and do now - manual process. 2nd set is the same set of data but as described below, this is what I'm looking to solve to.

What I'm wanting to do is create a dynamic Name Range whereby Name is based on "Family" in col V, Product in col W = "Accessory", Value is "Description" in col C. The Name Range I then use in other formula's in my workbook.

Current state:
In the top set of data, it is sorted by "Family" (col V) and "Product" col W, so when I create a Name Range for "Description" col C for each "Family in in col V and Product col W = "Accessory", I select $C$2:$C$13. Name of range I have given is "Smith". This is very manual when I want to add more data because I have to re-sort the data and re-adjust the "Refers to" range, to increase it by the number of new rows added that have Family name "Smith" and are an "Accessory" in col W.

Ideal end state:
2nd set of data. Instead of the data being sorted, I'm looking for a to create a dynamic name range by putting a formula in the "Refer to" field of the Name Manager dialogue box which will mean the data doesn't have to be sorted. As more data is added, which is very regularly, the Name Range which automatically pick up the new data that meets the criteria. In the data set I've created a name range called Smith. This Value is column C (description), based on column V = "Smith" and column W = "Accessory". I ultimately will create a Name Range which refers to col C, for each Family (col V) where col W = "Accessory".
On the second sheet in the example file called "Form", I have showed a simple example of what I will ultimately use the Name Range for.

Apologies for the detail description to paint of picture of what I'm trying to do.

Thanks.

[Glenn Kennedy]

Yes. You are over complicating things. This sheet replicates what you had, from unsorted data. It's probably not what you want... yet. So in your next post focus on the FINAL outcome rather than your proposed way of getting there...