Need Help writing a macro
 

Root Year Quarter Filename Type


='c:\Documents\ 2004\ Quarter 3\ [Test .xls]
Data Range
Performance'!$A$2:$J$402


Formula
='c:\Documents\2004\Quarter 3
[Test.xls]Performance'!$A$2:$J$402

I would like to define Name "CompanyA" (i.e, insert name, company A) t
be linked to formula above. The only problem is that I want the link t
be dynamic so when I change the top of the input sheet (highlighted i
yellow) the formula changes which then changes the link that is bein
used to define company A. Any help writing this macro? Thank you al
in advance.

I have made an example excel file but I do not know how to post on th
forum. Please let me know if you want to see the file and I willl sen
it to you via email. Thank you all very very much

--
Message posted from http://www.ExcelForum.com

Need Help writing a macro
 

I presume that you want to change [Test.xls} to another
name defined in a cell at the top of the page.

If this is the case then create a variable to contain the
name as defined in the relevant cell.
then use the variable in the path defintiion.

eg

compname=activesheet.range("A1").value

="c:\Documents\2004\Quarter 3\"& compname & "Performance!
$A$2:$J$402"

Hope this is what ytou are looking for.

BOL
DavidC
-----Original Message-----
Root Year Quarter
Filename Type


='c:\Documents\ 2004\ Quarter 3\ [Test
.xls]
Data Range
Performance'!$A$2:$J$402


Formula
='c:\Documents\2004\Quarter 3\
[Test.xls]Performance'!$A$2:$J$402


I would like to define Name "CompanyA" (i.e, insert name,
company A) to
be linked to formula above. The only problem is that I
want the link to
be dynamic so when I change the top of the input sheet
(highlighted in
yellow) the formula changes which then changes the link
that is being
used to define company A. Any help writing this macro?
Thank you all
in advance.

I have made an example excel file but I do not know how
to post on the
forum. Please let me know if you want to see the file
and I willl send
it to you via email. Thank you all very very much.


---
Message posted from http://www.ExcelForum.com/

.