Refering to a sheet in a cell

H

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
 
P

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
 
J

Jarek Kujawa

regarding "all the assisting formating" functions CANNOT do that

as regs hyperlinks I wasnot able to find a solution
 
H

houghi

Pete_UK said:
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.
Click to expand...

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
 
P

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
 
H

houghi

Pete_UK said:
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.
Click to expand...

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
 
P

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

Pete_UK said:
If the file is closed then the formula will return an error, so you
can trap this with an amendment like:
Click to expand...
=IF(ISERROR(INDIRECT("'[Weekly.xls]"&A1&"'!$D$7")),"no update - open
weekly.xls",INDIRECT("'[Weekly.xls]"&A1&"'!$D$7"))
Click to expand...
Hope this helps.
Click to expand...

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
Click to expand...
 
H

houghi

Pete_UK said:
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.
Click to expand...

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
 
P

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
 
Top