VLOOKUP not bringing back new data

[veggie_price]

Hello.

I hope someone can help - I've searched the internet numerous times and cannot locate the the answer thus far!

I have a sheet which has a number of VLOOKUP functions that brings back personal details from an employee number (first name, last name, mobile phone number etc.). The sheet it pulls back the data from is in another workbook.

The sheet has always worked fine, however when I add new people to the data sheet the VLOOKUP function fails to work. In fact when I go and change an existing entry it fails to bring back the change also! Randomly it fixes itself, but frustratingly I have no idea why it doesn't work.

the VLOOKUP formula encompasses the entire table, so no issue with adding more rows. It brings back existing data fine so the formula works. I save the table - no luck. I sort the data - no luck. I ensure the employee numbers have no spaces etc. and are in normal text - no luck. Then suddenly (days later perhaps) it works!

Any ideas?

 

[Asala42]

The random and occasional sudden fix is suspicious. Here are some things to check when it the vlookup is not working:

1) Check Formula Options to confirm that calculation is set to Auto. If it's set to Manual, the issue may be that you just need to hit F9 to force the formulas to calculate.

2) Confirm you do not have a Circular Reference in the spreadsheet. You can check by looking at the bottom bar of the spreadsheet - if you see the word "Circular" then you probably need to clear it before you can trust your formulas in that file.

In the meantime, feel free to post the formula itself and we'll review it for possible errors. Another possibility that comes to mind is if you use the INDIRECT function within your formula you'd need the reference file open for the vlookup to work properly.

 

[veggie_price]

Thanks for getting back to me.

Unfortunately I can't copy the formula yet as my work has prevented external emails at the moment whilst it upgrades its email client!

I have checked what you suggest and no issues there. HOWEVER, I have come across the issue as such - although I don't quite understand it still!

I have locked the sheets to prevent the formulas being over written etc. Now, when new employees are added to the data sheet the sheets aren't bringing back the new data. If I unlock the sheet and then click in the cells containing the formula, highlight a piece of the string and press ENTER the formula brings back the new employee's details. However, if I don't highlight the formula in the cell and simply type an existing employee number in place of the new employee number, the formula still brings back that employee's details.

It is all very strange! Any idea?