Referencing a worksheet on the server using VLOOKUP?

Using VLOOKUP, I am trying to reference a value in a worksheet on the server. I've never tried referencing anything on a server and I'm not even sure this will work. Is this even possible? Does the worksheet need to be open or can it remain closed?

Here, in more detail, is what I'm trying to do (apologies if this is confusing):

I have two worksheets, one on the server (#1) and one I'm trying to create (#2).

In worksheet #2, using VLOOKUP and this convention (without quotes) "VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])"
, I type in this formula: =vlookup(A1,'[Results.xlsx]Sheet1'!$A$1:$A$10,3,FALSE). A1 is the lookup value from worksheet #2. The Results.xlsx sheet, as well as the range, are for worksheet #1 on the server. I'm also referencing column 3 and looking for an exact match.

If worksheet #1 is on the server at \\10.XX.XX.XX\Production\Test\Results.xlsx, then how do I reference that in the formula?

Additionally, the values on worksheet #1 in cells A1:A10 are the results returned by a formula in that worksheet. For example, the formula in A1 might be =SUM(B1:G1). I want to use VLOOKUP to return the result in A1. Also, the result in Column 3 of worksheet #1 is the result of a formula as well.

Excel is accepting my formula, but it's returning #N/A.

I'm sure my description is confusing, to say the least, but any help would be greatly appreciated. If you have any questions about anything I've typed, please let me know so I can clarify.

This community has helped me in the past, and I'm hoping it will be able to help me once again.

Thank you!

Chris

You just need to use the server location in stead of your hardisk location you nomally use,
or you can try a easier way, open your excel file on #2, then make the formula you want in #3, then save. Next time you don't need to open the source file again.

Hope everyone had a great Labor Day weekend! Now that we're all back at work, I'm still having trouble with my file...

Per your suggestion, I opened up both worksheets and entered the formula into the second worksheet, then saved. Unfortunately, it's still not working -- I believe I'm doing something wrong.

I've attached a condensed version of the file I'm working on with two sheets to represent the two individual files. The sheets are labeled "On Server" and "On Hard Drive" to represent their physical locations, and I've included file names and locations on lines 1 and 2 of both sheets for reference (NOTE: not actual file names/locations )

Sheet "On Server" is the one I'm trying to reference in "On Hard Drive". Basically, I'm trying to pull the Yield Percentage for a serial number from the "On Server" sheet.

I hope I've provided enough information.

Thanks, again!

Chris

I have used formulas that reference workbooks on the network.
It does work, but as I understand, when you open your workbook it will ask you in the beginning if you want to update. If you say yes, it will go out and get the required values from the workbook on the network. If the workbook on the network requires a password, it will ask for that, otherwise that workbook doesn't have to be open.

I fixed the formula on your workbook. In the VLOOKUP formula, you need to define the Array. Your formula had A6:A10 instead of A6:I10.

I have also pasted a typical reference to a network workbook. The example pated is referencing Cell F4.

Hope this helps.
modytrane

Sweeeet! It works! I realized, after seeing your correction to my formula, that I had incorrectly defined the table_array (A6:A10 instead A6:I10).

I also noticed that the formula automatically inserted the network path to the file after I closed the file residing on the server. Then, when I reopened the server-side file, the network path disappeared and only the file name remained. Very interesting.

I then copied the formula to the remaining cells in the column of the worksheet on my hard drive and everything displayed correctly!

Thank you, modytrane and sglife, for your help with my "issue".

Best Regards,

Chris

Hi

We are using vlookup in excell 2003, we have all the excell files stored in network server (server1).

All the excell are accessed from network and updated in network folder only.

Server1 suddenly crashed, of course we had a backup of all the data.
We move all the data to new server (server2).

we maintained the same folder structure of server1 in new server.

Now the problem we face is in the vlookup formula all the cell values that is refering to server1 is showing Ref error, How do we change the server1 to server2 globally.

The following is the example of the formula

=VLOOKUP(B68,'\\Server1\payroll\FORMAT1\2009-10\PAYROLL\Salary Calulcation - 09-10\[July 09.xls]Attendance'!$B$4:$AD$63,12,0)

Thanks

Hi

We are using vlookup in excell 2003, we have all the excell files stored in network server (server1).

All the excell are accessed from network and updated in network folder only.

Server1 suddenly crashed, of course we had a backup of all the data.
We move all the data to new server (server2).

we maintained the same folder structure of server1 in new server.

Now the problem we face is in the vlookup formula all the cell values that is refering to server1 is showing Ref error, How do we change the server1 to server2 globally.

The following is the example of the formula

=VLOOKUP(B68,'\\Server1\payroll\FORMAT1\2009-10\PAYROLL\Salary Calulcation - 09-10\[July 09.xls]Attendance'!$B$4:$AD$63,12,0)

Thanks