Links to other workbook
 

I have a series of workbooks which access another workbook to complete data.

Some of this is done with data validation by creating a name linked to the
external data. The rest is done using VLOOKUP based on the entries cells
filled with data validation. This all used to work well until I changed the
location of the external data file (moved from My Documents to C:\).

I know I need to change the target in the sheets and this worked fine in the
first one, but the second seems to be causing problems.

When I open the workbook, I'm prompted that the sheet contains external data
and asks if I want to update it. Whne I click Yes, a dialog box opens saying
File Not Found and displaying the root directory where the file is.
Can anyone see anything wrong with the formulae below (copied while Test
Equipment List.xls is open)?
Named ranges
='[Test Equipment List.xls]Sheet1'!$E$5:$E$30
='[Test Equipment List.xls]Sheet1'!$A$5:$A$30
Formulae
=IF(A36="","",VLOOKUP(A36,'[Test Equipment
List.xls]Sheet1'!$A$5:$C$30,2,FALSE))
=IF(A36="","",VLOOKUP(A36,'[Test Equipment
List.xls]Sheet1'!$A$5:$C$30,3,FALSE))
='[Test Equipment List.xls]Sheet1'!$G$5

I've used Find to locate all references of Test and these are the only ones
on the sheet.
I've looked through all the named ranges and the only ones referring to an
external workbook are those listed above, all the others refer to a hidden
sheet in the local workbook.

Any suggestions will be very gratefully received.

Ian

Links to other workbook
 

"Ian" wrote in message
...
I have a series of workbooks which access another workbook to complete
data.

Some of this is done with data validation by creating a name linked to the
external data. The rest is done using VLOOKUP based on the entries cells
filled with data validation. This all used to work well until I changed
the
location of the external data file (moved from My Documents to C:\).

I know I need to change the target in the sheets and this worked fine in
the
first one, but the second seems to be causing problems.

When I open the workbook, I'm prompted that the sheet contains external
data
and asks if I want to update it. Whne I click Yes, a dialog box opens
saying
File Not Found and displaying the root directory where the file is.
Can anyone see anything wrong with the formulae below (copied while Test
Equipment List.xls is open)?
Named ranges
='[Test Equipment List.xls]Sheet1'!$E$5:$E$30
='[Test Equipment List.xls]Sheet1'!$A$5:$A$30
Formulae
=IF(A36="","",VLOOKUP(A36,'[Test Equipment
List.xls]Sheet1'!$A$5:$C$30,2,FALSE))
=IF(A36="","",VLOOKUP(A36,'[Test Equipment
List.xls]Sheet1'!$A$5:$C$30,3,FALSE))
='[Test Equipment List.xls]Sheet1'!$G$5

I've used Find to locate all references of Test and these are the only
ones
on the sheet.
I've looked through all the named ranges and the only ones referring to an
external workbook are those listed above, all the others refer to a hidden
sheet in the local workbook.

Any suggestions will be very gratefully received.

Ian

Links to other workbook
 

Sorry about the duplicate post a minute or two back.

OK. This is wierd!!
I cut/pasted the cells with formuale and data validation to another workbook
and copied them back bit by bit.

=IF(A36="","",VLOOKUP(A36,'[Test Equipment
List.xls]Sheet1'!$A$5:$C$30,2,FALSE))

This formula is fine...

=IF(A36="","",VLOOKUP(A36,'[Test Equipment
List.xls]Sheet1'!$A$5:$C$30,3,FALSE))

....but this one isn't

The only difference is the column being returned. The first formula looks
for a match between A36 and column A in the lookup range then returns the
value in column B. The second formula should do exactly the same matching,
but return the value in column C but comes up with "File Not Found".

I must be missing something really stupid, but what?

Ian

Links to other workbook
 

Sorted, but I don't know why.

I deleted the second formula and unmerged the 3 cells it occupied so I could
copy the formula from the "working" location (2 merged cells). I then edited
the formula to correct the cell reference (A36) and the column (changed 2 to
3), remerged the cells and it works!

So it appears I've got EXACTLY then same formula as I originally had, but
now it works!!!

Very odd!

Ian

"Ian" wrote in message
...
Sorry about the duplicate post a minute or two back.

OK. This is wierd!!
I cut/pasted the cells with formuale and data validation to another
workbook and copied them back bit by bit.

=IF(A36="","",VLOOKUP(A36,'[Test Equipment
List.xls]Sheet1'!$A$5:$C$30,2,FALSE))

This formula is fine...

=IF(A36="","",VLOOKUP(A36,'[Test Equipment
List.xls]Sheet1'!$A$5:$C$30,3,FALSE))

...but this one isn't

The only difference is the column being returned. The first formula looks
for a match between A36 and column A in the lookup range then returns the
value in column B. The second formula should do exactly the same matching,
but return the value in column C but comes up with "File Not Found".

I must be missing something really stupid, but what?

Ian