Formula within filename link

[Bazy2k]

Hi guys...

Ok each month i recieve a excel worksheet which contains data which i need
to consolidate with other familiar sheets...

In my consolidation schdeule what i normally do is painfully copy and paste
each section of data from the first workbook into the consolidation
workbook...

Now i wanted to create a formula which just links to the first workbook from
the consolidation workbook, this is obviously done by clicking and linking
the two, giving me a formula in each cell eg.
T:\Folder\Folder\Folder\2009 - 2010\[Aug 09.xls]Sheet1!A1

Now i wanted to ask if this pathway can include references to cells so that
when i change the date to 'Sept 09.xls' in cell A3, the pathway will change
to the Sept 09 file and pick those numbers automatically...
E.g. T:\Folder\Folder\Folder\2009 - 2010\["A3"]Sheet1!A1

I hope what im trying to ask for is clear.

Thanks

 

[Pete_UK]

You would normally use INDIRECT to do that, i.e. build up an address
as a string. However, this will only work if the workbook is open - is
this how you imagined it working?

There is an alternative, which is to download the free add-in morefunc
as this includes a function INDIRECT.EXT which can be used with closed
workbooks.

Hope this helps.

Pete

 

[MA]

Hi,

One very basic, quick and crude way is to select all the cells containing
the hyperlink formula and press ctrl+f to activate the find and replace
window, write the existing file name in the "find what" box and write the new
file name in the "replace with" box and click replace all.

In you below example try this:
type "Aug 09.xls" in the find what box and
type "'Sept 09.xls" in the replace with box and
press replace all button.

Do not forget to select the cells containing the hyperlink.

best of luck.

MA

You would normally use INDIRECT to do that, i.e. build up an address
as a string. However, this will only work if the workbook is open - is
this how you imagined it working?

There is an alternative, which is to download the free add-in morefunc
as this includes a function INDIRECT.EXT which can be used with closed
workbooks.

Hope this helps.

Pete

Hi guys...

Ok each month i recieve a excel worksheet which contains data which i need
to consolidate with other familiar sheets...

In my consolidation schdeule what i normally do is painfully copy and paste
each section of data from the first workbook into the consolidation
workbook...

Now i wanted to create a formula which just links to the first workbook from
the consolidation workbook, this is obviously done by clicking and linking
the two, giving me a formula in each cell eg.
T:\Folder\Folder\Folder\2009 - 2010\[Aug 09.xls]Sheet1!A1

Now i wanted to ask if this pathway can include references to cells so that
when i change the date to 'Sept 09.xls' in cell A3, the pathway will change
to the Sept 09 file and pick those numbers automatically...
E.g. T:\Folder\Folder\Folder\2009 - 2010\["A3"]Sheet1!A1

I hope what im trying to ask for is clear.

Thanks