Formula within filename link

B

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
 
P

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
 
M

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

Pete_UK said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Edit Link Dialog Box 1
summarizing data from various workbooks 4
SUMIF Formula Help 8
Index(Match) question 4
Copy Past Values 14
Calc Date Based on Year 3
Formula 1
Consolidation refresh (update) 0

Top