Refering to a sheet in a cell

[houghi]

I have something that refers to another cell in another file:
='[Weekly.xls]17'!$D$7

This works great. However 17 is the weeknumber and each week I would
like to refer to another week and another tab so I will place the
weeknumber in A1 (in result.xls)

What I would like is to point to that automagicaly. Something like

='[Weekly.xls](A1)'!$D$7

But that doesn't work. Any ideas?

houghi

 

[Jarek Kujawa]

=INDIRECT("'[Weekly.xls]"&A1&"'!$D$7")

 

[houghi]

=INDIRECT("'[Weekly.xls]"&A1&"'!$D$7")

Thanks, works great.

houghi

 

[Pete_UK]

Note that INDIRECT, as proposed by Jarek, will only work with open
files, so you will need to ensure that Weekly.xls is open each time
you use result.xls.

Hope this helps.

Pete

 

[Jarek Kujawa]

thks Pete

 

[Jarek Kujawa]

regarding "all the assisting formating" functions CANNOT do that

as regs hyperlinks I wasnot able to find a solution

 

[houghi]

Note that INDIRECT, as proposed by Jarek, will only work with open
files, so you will need to ensure that Weekly.xls is open each time
you use result.xls.

Hope this helps.

It not so much helps as informs. ;-)

Is there a way to do it without Weekly.xls being open, even if it means
that you need to select 'no update'?

houghi

 

[Pete_UK]

If the file is closed then the formula will return an error, so you
can trap this with an amendment like:

=IF(ISERROR(INDIRECT("'[Weekly.xls]"&A1&"'!$D$7")),"no update - open
weekly.xls",INDIRECT("'[Weekly.xls]"&A1&"'!$D$7"))

Hope this helps.

Pete

 

[houghi]

If the file is closed then the formula will return an error, so you
can trap this with an amendment like:

=IF(ISERROR(INDIRECT("'[Weekly.xls]"&A1&"'!$D$7")),"no update - open
weekly.xls",INDIRECT("'[Weekly.xls]"&A1&"'!$D$7"))

Hope this helps.

This shows only the message. Perhaps it would be better to open
Weekly.xls with a script automaticaly when the file is opend.

If I understand, there won't be an other way. No probs, thanks anyway
for the pointers.

houghi

 

[Pete_UK]

Well, there is a free download add-in, morefunc, which has the
function INDIRECT.EXT which is meant to be equivalent to INDIRECT but
it works with closed workbooks. I don't have it myself, but I've seen
many recommendations on other posts, so you might like to give it a
try - search the Excel groups for morefunc and I'm sure you will find
the website.

Hope this helps.

Pete

If the file is closed then the formula will return an error, so you
can trap this with an amendment like:

=IF(ISERROR(INDIRECT("'[Weekly.xls]"&A1&"'!$D$7")),"no update - open
weekly.xls",INDIRECT("'[Weekly.xls]"&A1&"'!$D$7"))

Hope this helps.

This shows only the message. Perhaps it would be better to open
Weekly.xls with a script automaticaly when the file is opend.

If I understand, there won't be an other way. No probs, thanks anyway
for the pointers.

houghi

 

[houghi]

Well, there is a free download add-in, morefunc, which has the
function INDIRECT.EXT which is meant to be equivalent to INDIRECT but
it works with closed workbooks. I don't have it myself, but I've seen
many recommendations on other posts, so you might like to give it a
try - search the Excel groups for morefunc and I'm sure you will find
the website.

Thanks. For now I just have included a bit of VBA:
Private Sub Workbook_Open()
Application.EnableEvents = False
Workbooks.Open Filename:= _
"Y:\=Contact Centre=\Reporting\Weekly.xls", _
UpdateLinks:=0
Windows("Template.xls").Activate
Application.EnableEvents = True
End Sub

This does the trick well enough for me. I will look into morefunc
however. http://xcell05.free.fr/morefunc/english/

houghi

 

[Pete_UK]

Thanks for feeding back. I'm glad you found the morefunc site - I read
last month that the site was down, but it seems ok now.

Pete