Referencing external workbooks

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

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 have
the other workbook open. Just punch out your formula as usual but when
you need to lookup off another workbook just switch to it and it should
pop in the syntax for future updating.

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

Ben


---
Message posted from http://www.ExcelForum.com/

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



-----Original Message-----
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 have
the other workbook open. Just punch out your formula as
usual but when
you need to lookup off another workbook just switch to
it and it should
pop in the syntax for future updating.

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

Ben


---
Message posted from http://www.ExcelForum.com/

.

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 off
that, otherwise use the 'Today()' function

so...

text(today(),"mmmm")

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

text(A1,"mmmm")

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

Try using which ever one suites your needs inside the file path, havent
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 formula
into the file name.

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

Let me know how it goes...

Ben


---
Message posted from http://www.ExcelForum.com/

Michael,

Just a thought here...

A formula referencing a location that does not exist will always return
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 be
set....


---
Message posted from http://www.ExcelForum.com/

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?


-----Original Message-----
Michael,

Just a thought here...

A formula referencing a location that does not exist
will always return
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 be
set....


---
Message posted from http://www.ExcelForum.com/

.

Sure,

If you create the formula with the names referencing sheets that dont
exist yet, you can just build in some error checking so that if the
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 0
instead of an error if the sheet were trying to access doesnt exist
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 put
whatever value you wish to display when the lookup returns an error.

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

Ben


---
Message posted from http://www.ExcelForum.com/