How to define multiple names quickly?

[Rnkhch]

Hello,

I need to define many names for chart series of the following type that allows for the chart to automatically adapt to expanding series:

name1=Excel!$E$36:INDEX(Excel!$E$36:$S$36,COUNT(Excel!$E$36:$S$36))

Is there any way to speed up this process, instead of defining them one by one in the name manager? I'm hoping I could perhaps write all my names in various rows along with their formulas in the column to the right and somehow import them into the name manager.

Alternatively, if this could be done through VBA, could someone suggest the code?

Thanks for any input!

 

[davesexcel]

Depending on the worksheet layout, you can highlight the range for example A1:C10.
Got to the formula tab and select "create from selection", select what heading to use as the named range.

 

[Rnkhch]

I see, thank you, this is interesting. But I'm not sure how to put my exact syntax in the defined names. I tried putting the syntax in C2 as in the attached picture; is this the right way?

 

[Alex Blakenburg]

Give this a try:
Put your names in column A and your formula in column B.
Make sure there is nothing under your list in column A (so that xlup finds the bottom or your list)
I have assumed your first name starts on Row 2 change it if this is not the case.

If only works if any cell references in the formulas are absolute ie use $ sign.
I tried it with and without putting a single quote in front of the formula in column B and it seemed to work either way.

Sub AddRngNames()

    Dim rng As Range, rCell As Range
    Dim nm As String
    Dim nmFormula As String
    
    Set rng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    
    For Each rCell In rng

        ThisWorkbook.Names.Add Name:=rCell.Value, RefersTo:=rCell.Offset(0, 1).Formula
    
    Next rCell

End Sub

 

[Rnkhch]

Thank you so much. I'm gonna test this this week and may reach out if I need help with the exact procedure (I'm a newb at VBA). I'll update.

 

[Rnkhch]

Wow, this works so well!!! This is a miracle! It saved me so much time. Thank you again

 

[Alex Blakenburg]

You're welcome. Glad we could help.