VLookups on Network

[dav135]

Hi, I've created a spreadsheet which looks at another on the network to
distinguish if the open file is the latest version. (ie. a report is
sent as V1 and saved on the local computer of the recipient, meanwhile
the report on the network is udated to V1.1 - when the user then opens
the original V1 report, the spreadsheet simply advises that there is a
newer verion).

This works fine for me, until the email with the report is actually
sent. Before the email is sent, the formula looks like this:

=VLOOKUP(F11,'J:\Marketing_New\Management
Information\[Index.xls]Management Information Index'!$B:$G,3,0)

However, when the email is sent, this formula then changes to this:

=VLOOKUP(F11,'C:\Marketing_New\Management
Information\[Index.xls]Management Information Index'!$B:$G,3,0)

Only a very slight change, but the J drive changes to the C drive??
Any ideas how to get around this?

Many Thanks!!

 

[Don Guillett]

have a look in the help index for INDIRECT which does not work on closed
workbooks.

 

[dav135]

Thanks, but I must be doing something wrong!!

My index file looks something like this:
ID Name Version Notes
123 MD 1.0 N/A

On my report i have a front sheet with the following:

ID <-- entered when the report is produced
Name <-- This is a vlookup on the ID number into col 2 of the index
file.
Version <-- Version entered when the report is produced.
Last Version <-- This is a vlookup on the ID number into col 3 of the
index file.
Notes <-- This is a vlookup on the ID number into col 4 of the index
file.


How would I get the indirect function to work with this - if its
possible??


Thanks again!

 

[Dave Peterson]

Maybe you could try referring to the UNC path instead of the mapped drive
letter.

=vlookup(f11,'\\someserver\someshare\marketing_new\......

Hi, I've created a spreadsheet which looks at another on the network to
distinguish if the open file is the latest version. (ie. a report is
sent as V1 and saved on the local computer of the recipient, meanwhile
the report on the network is udated to V1.1 - when the user then opens
the original V1 report, the spreadsheet simply advises that there is a
newer verion).

This works fine for me, until the email with the report is actually
sent. Before the email is sent, the formula looks like this:

=VLOOKUP(F11,'J:\Marketing_New\Management
Information\[Index.xls]Management Information Index'!$B:$G,3,0)

However, when the email is sent, this formula then changes to this:

=VLOOKUP(F11,'C:\Marketing_New\Management
Information\[Index.xls]Management Information Index'!$B:$G,3,0)

Only a very slight change, but the J drive changes to the C drive??
Any ideas how to get around this?

Many Thanks!!

 

[dav135]

So simple yet so effective - that works perfectly.

Thanks v.much!!