range name question

[Gary Keramidas]

i'll simplify a little here. i have a separate workbook with rates and some
named ranges. let's say one is Joe_Tier1 and another is Bob_Tier1.

then bob and joe have separate workbooks.

if i have their name in A1. how would i write a formula to replace "Joe" wit the
value in A1?

Rates.xls!Joe_Tier1*G58

 

[Gary Keramidas]

right now i'm building the formula on a worksheet open event. i can do it in vb,
just can't figure it out with a cell formula

 

[Guest]

Let's say that Joe_Tier1 referred to B2:C3 and that cell A1 contains frank

running:

Sub Macro1()
Dim s As String
Dim s2 As String
s = Cells(1, 1).Value
s2 = Replace("Joe_Tier1", "Joe", s)
ActiveWorkbook.Names.Add Name:=s2, RefersToR1C1:= _
"=Sheet1!R2C2:R3C3"
End Sub

will create a new range named frank_Tier1 that also covers B2:C3. You can
delete the old name manually or by a :

ActiveWorkbook.Names("Joe_Tier1").Delete

 

[Bob Phillips]

Do you just mean

=INDIRECT("Rates.xls!"&A1&"_Tier1")*g58

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Gary Keramidas]

yes, bob, exactly. i was trying the indirect between the &&'s

thanks