Need help linking into worksheets on workbooks

[rfisher33]

Don't know if this is possible but I am creating a spreadsheet to
access data from various callout rotas for work.

I have setup a worksheet which chooses Department and Team from Combo
boxes and displays the contents of a particular cell below them. This
particular cell will pull information from another workbook that is
external. The information in this other workbook is divided up into
worksheets that are named with the date that it would be at the
beginning of each week so we can plan callout in advance. Is there a
way to get the cell that is pulling the data to reference what the
date is at the start of the current week and then choose the worksheet
it looks at in the external workbook? Sounds very complicated I know
but i'm sitting here at 3:48 am trying to work this one out.

Any help is gratefully appreciated.

Rob

 

[Pete_UK]

Hope you are not still there ...

Normally you would use the INDIRECT function to build up a range
reference and use this within your formula. However, INDIRECT will
only work on external files if they are open (you don't make it clear
if they are or not).

Post back if you need any further help - I don't check this group very
often.

Pete

 

[Pete_UK]

Hi Rob,

As you have found, =indirect() won't work when the sending file is
closed.
However, Laurent Longre has an add-in (morefunc.xll) at:

http://xcell05.free.fr/

This includes the function =indirect.ext() that may help you with
closed
files.

Hope this helps.


Pete