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

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

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...

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

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
--
Gary's Student


"Gary Keramidas" wrote:


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

Do you just mean

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...

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

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

thanks

--


Gary


"Bob Phillips" wrote in message
...
Do you just mean

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...

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