VLOOKUP Head Scratcher

[DPChristman]

I have an odd situation.

One of my forms contains a VLOOKUP to a spreadsheet containing employee information.

Enter the employee ID #, and the employee information is automatically populated.

This is repeated about 10 times on the form, so you can enter the employee ID for up to 10 people, and have it populate the data

The spreadsheet that the VLOOKUP pulls from is on a shared network drive.

The problem is a strange one.

Some of my users are reporting that when they enter the employee ID, it populates for the first few employees, then starts giving an #N/A message for the others.

I had them send me the format they were working on. Although they were receiving the #N/A message in the auto-pop fields, everything was populated normally when I opens the form.

Important note, I am not directly connected to the corporate server. I am connecting through a VPN (I work from home), just like they do. yet, I have no problems.

I have examined, and re-examined the formulas, and they all work correctly.

I thought that this may be related to the fact that the users also use an air card to connect to the internet, but I am told this happens whether they are on air card, hard wired or wireless.

I am baffled. Any thoughts?

 

[Jeffrey Mahoney]

Are you anchoring ($) your ranges inside the VLOOKUP formula?

I know, I have to ask such questions.

 

[DPChristman]

Yep (I changed the folder names to make them easier to fit)
=VLOOKUP($A76,'S:\FOLDER1\FOLDER2\FOLDER3\FORMAT\[EMPLOYEE LIST.xlsx]Employee Listing'!$A:$Z,6,0)
A76 represents the location where the employee ID is entered. It can't be a constant because the location changes for each entry

 

[Jeffrey Mahoney]

Without being there and seeing it happen, it's hard for me to come to a conclusion. Usually I start with the simple things. Making sure that those ID's are actually in the database. Making sure that the ID cells are formatted as numbers and not text (in both the form and the database). Making sure the formula on the form is correct all the way down the column. On that last note, if somebody moves a cell, the formula will follow as you know; so it may look like it's referencing the cell on the same row, but in fact it's referencing another row.

That's all I can offer until more information comes in.

 

[DPChristman]

Well one way that I know that the formulas are not likely the issue is that I had one of the people send me a failed document, where the vlookup was producing a #N/A error.
When I opened the attachment, the All the employee information was loaded. So, the correct ID was entered, the vlookup was valid, and the information it was pulling was available.
Like I said, it is bizarre.
The only other possibility is that they are not clicking on Enable Content, when the format opens (although they tell me they do)
Hence, the headscratcher

 

[Jeffrey Mahoney]

Enable Content may or may not update the links. They should check the settings in the Trust Center under External Content.

 

[AkaTrouble]

enable content will not be the issue ( i don't think) as the form would not work either,

have you tried sending the file back that they sent you after you have opened it and simply saved it to see if the boxes then populate when they open it