variable path in a link

[Christy]

Is there a way to use a variable in the path of a link. I would like to be
able to define the variable by typing it is a specific cell then update all
my links accordingly

For example say I have workbooks named pOffice22, pOffice23 etc in a
folder. All of these files are the same except they contain data from
different weeks. I have a summary workbook that I use to perform various
reports on this data. The data is transferred into the summary workbook via
links.

I would like to be able to type 22 in a cell and get the data from file
pOffice 22 or type 23 in the cell and get the data from file pOffice23.

Any help will be greatly appreciated
Christy

[Gary Brown]

Take a look at the InDirect function in HELP

"Christy" <[email protected]> wrote in message
news:[email protected]...
> Is there a way to use a variable in the path of a link. I would like to
> be
> able to define the variable by typing it is a specific cell then update
> all
> my links accordingly
>
> For example say I have workbooks named pOffice22, pOffice23 etc in a
> folder. All of these files are the same except they contain data from
> different weeks. I have a summary workbook that I use to perform various
> reports on this data. The data is transferred into the summary workbook
> via
> links.
>
> I would like to be able to type 22 in a cell and get the data from file
> pOffice 22 or type 23 in the cell and get the data from file pOffice23.
>
> Any help will be greatly appreciated
> Christy

[Christy]

Thanks Gary but I don't see how that helps me.

I have a workbook that will get data from several different (closed)
workbooks via links. I want the paths in those links to be variable and be
able to define that variable in a cell.

If the source folder contains workbooks named; xName1, yName1, zName1,
xName2, yName2 and zName2 - I want to type 2 in a predetermined cell and
link to the data in xName2, yName2 and zName2.

"Gary Brown" wrote:

> Take a look at the InDirect function in HELP
>
> "Christy" <[email protected]> wrote in message
> news:[email protected]...
> > Is there a way to use a variable in the path of a link. I would like to
> > be
> > able to define the variable by typing it is a specific cell then update
> > all
> > my links accordingly
> >
> > For example say I have workbooks named pOffice22, pOffice23 etc in a
> > folder. All of these files are the same except they contain data from
> > different weeks. I have a summary workbook that I use to perform various
> > reports on this data. The data is transferred into the summary workbook
> > via
> > links.
> >
> > I would like to be able to type 22 in a cell and get the data from file
> > pOffice 22 or type 23 in the cell and get the data from file pOffice23.
> >
> > Any help will be greatly appreciated
> > Christy
>
>
>

[Tim Williams]

=INDIRECT("[Week" & A1 & "]Sheet1!$A$1")

Where (eg) A1 has your week number

Tim



"Christy" <[email protected]> wrote in message
news:[email protected]...
> Thanks Gary but I don't see how that helps me.
>
> I have a workbook that will get data from several different (closed)
> workbooks via links. I want the paths in those links to be variable
> and be
> able to define that variable in a cell.
>
> If the source folder contains workbooks named; xName1, yName1,
> zName1,
> xName2, yName2 and zName2 - I want to type 2 in a predetermined
> cell and
> link to the data in xName2, yName2 and zName2.
>
> "Gary Brown" wrote:
>
>> Take a look at the InDirect function in HELP
>>
>> "Christy" <[email protected]> wrote in message
>> news:[email protected]...
>> > Is there a way to use a variable in the path of a link. I would
>> > like to
>> > be
>> > able to define the variable by typing it is a specific cell then
>> > update
>> > all
>> > my links accordingly
>> >
>> > For example say I have workbooks named pOffice22, pOffice23 etc
>> > in a
>> > folder. All of these files are the same except they contain data
>> > from
>> > different weeks. I have a summary workbook that I use to perform
>> > various
>> > reports on this data. The data is transferred into the summary
>> > workbook
>> > via
>> > links.
>> >
>> > I would like to be able to type 22 in a cell and get the data
>> > from file
>> > pOffice 22 or type 23 in the cell and get the data from file
>> > pOffice23.
>> >
>> > Any help will be greatly appreciated
>> > Christy
>>
>>
>>

[Bob Phillips]

INDIRECT will not work on closed workbooks. For that you need Harlan's Pull
routine http://tinyurl.com/5zsxx

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
news:[email protected]...
> =INDIRECT("[Week" & A1 & "]Sheet1!$A$1")
>
> Where (eg) A1 has your week number
>
> Tim
>
>
>
> "Christy" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Gary but I don't see how that helps me.
> >
> > I have a workbook that will get data from several different (closed)
> > workbooks via links. I want the paths in those links to be variable
> > and be
> > able to define that variable in a cell.
> >
> > If the source folder contains workbooks named; xName1, yName1,
> > zName1,
> > xName2, yName2 and zName2 - I want to type 2 in a predetermined
> > cell and
> > link to the data in xName2, yName2 and zName2.
> >
> > "Gary Brown" wrote:
> >
> >> Take a look at the InDirect function in HELP
> >>
> >> "Christy" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Is there a way to use a variable in the path of a link. I would
> >> > like to
> >> > be
> >> > able to define the variable by typing it is a specific cell then
> >> > update
> >> > all
> >> > my links accordingly
> >> >
> >> > For example say I have workbooks named pOffice22, pOffice23 etc
> >> > in a
> >> > folder. All of these files are the same except they contain data
> >> > from
> >> > different weeks. I have a summary workbook that I use to perform
> >> > various
> >> > reports on this data. The data is transferred into the summary
> >> > workbook
> >> > via
> >> > links.
> >> >
> >> > I would like to be able to type 22 in a cell and get the data
> >> > from file
> >> > pOffice 22 or type 23 in the cell and get the data from file
> >> > pOffice23.
> >> >
> >> > Any help will be greatly appreciated
> >> > Christy
> >>
> >>
> >>
>
>