How to add a variable as a parameter for a link?

Will it be possible to add a variable as a parameter for the link? as shown
below format, therefore, I can change its parameter based on certain cell
value to link different spreadsheets? for example

Original
'[filename.xls]6220.HK'!$E$2

Modified approach
'[filename.xls]C2.HK'!$E$2 , where C2 = 6220, which equals to linking
'[filename.xls]6220.HK'!$E$2

Thank for any suggestion
Eric

You have one response in .misc.

Eric wrote:
>
> Will it be possible to add a variable as a parameter for the link? as shown
> below format, therefore, I can change its parameter based on certain cell
> value to link different spreadsheets? for example
>
> Original
> '[filename.xls]6220.HK'!$E$2
>
> Modified approach
> '[filename.xls]C2.HK'!$E$2 , where C2 = 6220, which equals to linking
> '[filename.xls]6220.HK'!$E$2
>
> Thank for any suggestion
> Eric

--

Dave Peterson

You can use INDIRECT

=INDIRECT("'[filename.xls]"&C2&".HK'!$E$2")

note that the workbook needs to be open

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


"Eric" <Eric@discussions.microsoft.com> wrote in message
news:A759D592-B95F-4A51-AA22-B472EAFEEBAD@microsoft.com...
> Will it be possible to add a variable as a parameter for the link? as
> shown
> below format, therefore, I can change its parameter based on certain cell
> value to link different spreadsheets? for example
>
> Original
> '[filename.xls]6220.HK'!$E$2
>
> Modified approach
> '[filename.xls]C2.HK'!$E$2 , where C2 = 6220, which equals to linking
> '[filename.xls]6220.HK'!$E$2
>
> Thank for any suggestion
> Eric

Thank everyone very much
Eric