use list to create named ranges

[gerenrich]

I have data that I am allowing user to drill down into one section at a time and using data validation "Indirect" statements referring to the previous cells. The supporting data is quiet large and I am looking for a way to automate the "named ranges" based on a column that i generate next to the column needing the "named ranges". Example attached if it helps to clarify.

Thanks in advance for the help.

 

[Rx_]

Since the data is large, wanted to check on this first.
"Names in a workbook Limited by available memory"
Are you looking for something like this?

Sub AddRanges()
    Dim wks         As Worksheet
    Dim cell        As Range
 
    Set wks = Worksheets("Sheet1")
 
    For Each cell In Intersect(wks.Columns("A"), wks.UsedRange)
        If Len(cell.Text) Then
            wks.Names.Add Name:=cell.Value, RefersTo:="=" & cell.Offset(, 1).Value
        End If
    Next cell
End Sub

Given that the data is huge, I have my reservations that using Named ranges is the best solution.

I too deal with very large data sets from SQL Server and apply rule-based modifications to Excel. One solution I just finished last week was correct, but took 26 mintues to generate the Excel worksheet. Last night, my modifide solution took around 30 seconds.

If you care to explain more details about your objective, I will try to get back to you. Perhaps there is a different approach to consider.

 

[gerenrich]

Thanks for that, and yes I can explain in more detail.
I have a list of FLOCs (items) that in this case are laid out as follows:

the mask is not consistent, but there is always a - (hyphen) between each section of the field which is being searched to see if it exists.
1st section being plant
2nd section being System
3rd being tank/zone
4th section being Code
5th section being prefix
6th section being Area
7th section being Suffix
For example:
31-01-001-CIP-001-01
plant is 31
System is 01 etc...

I need to build either a tree view for users to search, or let them select first by plant, then system, then tank/zone etc till they find the complete item only if it exists in the current data.

Not all items contain a full mask, there is one for the plant only, one for the plant and system for each system etc for example:
31 31-01 31-01-001
31-01-001-CIP-001-01
attached is an example list of the items with the start of my search.