Referencing external workbooks

[Michael]

Hi,

I'm trying to find a way to set up a table in excel that
references external workbooks. No problem with that part,
even with workbooks that are yet to be created.

I've got external workbooks that exist, but need to set
the table up to reference worksheets in that file that
will be created in the future. No problems occur if the
file doesn't exist yet, but excel starts to reference
incorrect worksheets if the file is already present.

Any help would be greatly appreciated.

Cheers

Michael

 

[bsullins]

By table do you mean Pivot table, or just a table of data?

When you need to reference other workbooks its pretty easy if you hav
the other workbook open. Just punch out your formula as usual but whe
you need to lookup off another workbook just switch to it and it shoul
pop in the syntax for future updating.

If you have an example of a formula it would help in understanding wha
your trying to do.

Be

 

[Michael]

Hi, thanks for the reply, am using a normal table that is
referencing data in another workbook.

eg. ='\\appmiaumelfnp01\mnathan$\data\[V3A.xls]July2004'!
$L$24

We're extracting monthly data to the workbook called
V3A.xls

A worksheet will be created in that file each month to
list the required data, in this example July2004 is the
worksheet.

In the data summary worksheet, where the external
reference is, I can it up to look at the data if the
source worksheet exists. But I want to prepare the
references to worksheets that will be created in the
source file in the future, eg. a worksheet called
December2004.

When I try to do so, excel keeps referencing the latest
existing worksheet, in this case for July.
I can get it to work fine if the source file does not
exist yet, so don't understand why I'm encountering these
problems.

Cheers

Michael

 

[bsullins]

Try using a formula to create the sheet name your looking for...

Example

If you have the date listed somewhere on the report you could go of
that, otherwise use the 'Today()' function

so...

text(today(),"mmmm")

This formula will give you the current month were in, formatted in th
way you specified in your example.

text(A1,"mmmm")

This one will give you the month name of the value in cell A1, chang
the cell to the one you have the date in if your using any dates on th
report

Try using which ever one suites your needs inside the file path, haven
tested this below but it should give you a good idea of how to do it

I added double quotes around the path so I could integrate the formul
into the file name.

eg
="'\\appmiaumelfnp01\mnathan$\data\[V3A.xls]"&text(today(),"mmmm")&"2004'!$L$24"

Let me know how it goes...

Be

 

[bsullins]

Michael,

Just a thought here...

A formula referencing a location that does not exist will always retur
an error unless you build some error trapping into the formula.

If you need some error checking try looking up the 'iserror' function
combine that with an if statment and your lookup and you should b
set...

 

[Michael]

Thanks for that, but I'm still not really sure how to set
up the reference in the cell.

Is there a way that I can say:
If the required worksheet is present in the source
workbook, extract this cell, if the worksheet is not
present, and error occurs, leave this cell as a zero?

 

[bsullins]

Sure,

If you create the formula with the names referencing sheets that don
exist yet, you can just build in some error checking so that if th
sheet doesnt exist it will return 0

ie.

here's the basic formula

=VLOOKUP(A2,[V3A.xls]July2004!$L$24,1,0)

Were going to combine the 'IF' and 'ISERROR' functions to show
instead of an error if the sheet were trying to access doesnt exis
yet.

=IF(ISERROR(VLOOKUP(A2,[V3A.xls]July2004!$L$24,1,0)),0,(VLOOKUP(A2,[V3A.xls]July2004!$L$24,1,0)))

Notice in between the lookups we have a 0, that is where you pu
whatever value you wish to display when the lookup returns an error.

Hope that helps, if you need anything else leave me a message

Be