Hi, i have multiple series that i want to define as a named ranges using the index formula
say row 1 is called payments
and row 2 is called phonecalls
I will be adding new data every month and i want my chart to update for each month. Earlier i have used the index formula that looks like this:
$B$3:INDEKS($B$3:$S$3;SAMMENLIGNE(9,99E+307;$B$3:$Q$3)) then i would define and save it to the defined named range. This is fine but when i have 50 ranges to define it takes too long and i also have to do it in several sheets and define in all my charts.
I have gotten close to defining one range using VBA i think...
The code i have used is :
Sub rangename()
Dim Rng1 As range
Set Rng1 = ActiveCell.Offset(0, 1)
With ActiveCell.Add.Formula = "=($B$3:INDEKS($B$3:$S$3;SAMMENLIGNE(9,99E+307;$B$3:$Q$3)))"
ActiveWorkbook.Names.Add Name:=Selection, RefersTo:=Rng1
End With
End Sub
For now i am trying to run the macro from a selected cell and define the range and save the range with the name of the selected cell. It gets the name right but wont save the dynamic range. It just saves the cell next to the selected cell as the range.
I am also using Norwegian Excel, but it is index and match that is used.
If you could help me to do one at a time to begin with that will sure help .
Regards
say row 1 is called payments
and row 2 is called phonecalls
I will be adding new data every month and i want my chart to update for each month. Earlier i have used the index formula that looks like this:
$B$3:INDEKS($B$3:$S$3;SAMMENLIGNE(9,99E+307;$B$3:$Q$3)) then i would define and save it to the defined named range. This is fine but when i have 50 ranges to define it takes too long and i also have to do it in several sheets and define in all my charts.
I have gotten close to defining one range using VBA i think...
The code i have used is :
Sub rangename()
Dim Rng1 As range
Set Rng1 = ActiveCell.Offset(0, 1)
With ActiveCell.Add.Formula = "=($B$3:INDEKS($B$3:$S$3;SAMMENLIGNE(9,99E+307;$B$3:$Q$3)))"
ActiveWorkbook.Names.Add Name:=Selection, RefersTo:=Rng1
End With
End Sub
For now i am trying to run the macro from a selected cell and define the range and save the range with the name of the selected cell. It gets the name right but wont save the dynamic range. It just saves the cell next to the selected cell as the range.
I am also using Norwegian Excel, but it is index and match that is used.
If you could help me to do one at a time to begin with that will sure help .
Regards
Last edited: