oh, that will not be a problem. I can name them R and L... so 11L and 11R....
Cool. Which one will be which? i.e. will the top one be L or R? Let me know, and I'll amend the sample file accordingly.
Don't use the sample file as is...I'm not quite finished with it.
Ok, I have tried your sample and it works. then I have tried to copy the formulas and the ranges into my workbook but nothing works... any suggestions?
Can't you just delete the existing sheet, and copy this sheet in to the workbook in it's place? (Right click the sheet tab, select Move or Copy, and select the name of the destination workbook from the dropdown at the top).
Otherwise, you'll have to do quite a bit of setup.
View attachment Sample.xlsb
Let's take a look at how it works. Click on each cell below the headings (i.e. A, B, C, 12P, 13P etc) and you'll see in the name box that every one of them has been given a name e.g. Area_A, Area_B, Area_C, Area_12P, Area_13P.
Also note that adding these names manually would be tedious, so I wrote some code that does it. Note - this code lives in my personal macro workbook i.e. it doesn't need to be in the actual sample file...it's code I will use again and again on different projects. If you're not familiar with what a personal macro workbook is, read
http://chandoo.org/wp/2013/11/18/using-personal-macro-workbook/
Code:
Sub CreateNamesFromTopRow()
Dim rCell As Range
Dim sCol As String
Dim sPrefix As String
Dim strPrompt As String
If TypeName(Selection) = "Range" Then
strPrompt = "I'll use the headings in the top row to name each range." & vbNewLine & vbNewLine
strPrompt = strPrompt & "OPTIONAL: You can enter a prefix below if you want, and I'll use it to prefix each Named Range with." & vbNewLine & vbNewLine
strPrompt = strPrompt & "Otherwise just push OK, and I'll u se the headings as is."
sPrefix = Application.InputBox( _
Title:="Please input a prefix if you want one...", _
Prompt:=strPrompt, _
Type:=2)
If sPrefix = "False" Then Exit Sub
For Each rCell In Selection.Rows(1).Cells
If rCell.Value <> "" Then ActiveWorkbook.Names.Add Fix_Name(sPrefix & rCell.Value), "=" & rCell.Offset(1).Address
Next rCell
End If
End Sub
To use this, you select a block of headings and one row of cells underneath - e.g. A4:R5 in your sample workbook - then run the code. It will prompt you to enter a Prefix - in this case we're using Area_ - and then when you push OK it will add names to the cells in the row below that heading. Note that you can't use this to set up names in those boxes where the headings are below the box...you'll have to do those ones manually
Also, on Sheet 1 you'll see that I've turned that list into an Excel Table (using the Ctrl + T keyboard shortcut). If you're not familiar with Excel Tables, read
http://chandoo.org/wp/2014/03/28/tables-pivottables-and-macros-music-to-your-ears/
Lastly, I have assigned names to a couple of the columns in that table. So I select the ULD column from B2 to B29 (i.e. the column excluding the heading) and typed ULD into the Name Box, and then did the same for the Position column, calling it Position. (I did this, because the VBA code uses these names to do things...and if I instead used the Table names in the VBA code, then if you were to rename those table column headings then the code wouldn't work)
Then I added this code to the Sheet2 code module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Identifies any Named Ranges that map directly to rng
Dim nm As Name
Dim strName As String
Dim varRow As Variant
Const strPrefix = "Area_"
If Target.Cells.Count > 1 Then Exit Sub
For Each nm In ActiveWorkbook.Names
If Replace(Replace(nm.RefersTo, "=", ""), "'", "") = ActiveSheet.Name & "!" & Target.Address And Left(nm.Name, Len(strPrefix)) = strPrefix Then
strName = nm.Name
Exit For
End If
Next
If strName <> "" Then
varRow = Application.Match(Target.Value, [ULD], 0)
If Err.Number = 0 Then
[Position].Cells(varRow).Value = Replace(nm.Name, strPrefix, "")
Else: MsgBox "Item does not exist in list"
End If
End If
End Sub
That code keeps track of what cells you change. If you change a cell with a named range assigned to it - and if that named range is prefixed with "Area_" - then the code goes "Aha...the user is doing something to the cells we care about". It works out which heading is involved by simply reading the name of the named range...e.g. if it was Area_12p then the code says "Aha...the user has just put something in the box associated with 12P. I better see what that something was, and if it's in that table on sheet 1 I better write 12P in the associated row in the Position column".
