excel horizontal lookup giving old data when source file is hidden

Hi,

Not sure if the title is clear, but the issue is complex.

A file (we call it the "destination file") is located in a public network folder and contains a horizontal lookup formula (in Dutch) :

=HORIZ.ZOEKEN(C4;'k:\Persoonlijk\HRM\Undisclosed\Team\Vakantieplanners\[vakantieplannerforcalculation.xlsm]Statistieken 1'!$12:$40;23;0)

What it does : based upon entry of the user name in cell C4, the formula retrieves a number in the source file. This source file is located in a non-public folder, to which I have access, nobody else does. I emphasize it because it may be cause of the problem. The reason I do this, is that the source file also contains confidential information.

The problem : when I enter someone's user name in C4, I always obtain the correct data (so the result is equal to the info in the source file). When other people enter their own name, they receive other info and it usually is info which is outdated. As if they "see" a previous version of the source file. So the info they receive, has always been correct on a certain moment in time, but it is not anymore.

Who can help me ? Thanks !

Originally Posted by jokkebal

What it does : based upon entry of the user name in cell C4, the formula retrieves a number in the source file. This source file is located in a non-public folder, to which I have access, nobody else does. I emphasize it because it may be cause of the problem. The reason I do this, is that the source file also contains confidential information.

You suspect correctly, if the data is in a place the user doesn't have access to then they won't be able to update the values.

Can you make a copy of the file that doesn't contain the confidential information and have the users point to that instead?

Hi Paul,
Thanks for your reply. It remains a mistery why it gives something at all in that case, and why it seems to give the next-to-last figure... Looks like the system keeps old files in layers in its memory, where I only see the upper layer and all other ones the one below it...
Anyhow, let's move on to your alternative. I was thinking of that as well, but this all happens in macros and I don't know how I can save a part of a worksheet in VBA. I explain :
The abovementioned source file is already an extract of one worksheet out of the "mother-workbook" using the VBA :

ThisWorkbook.Sheets("Ingaveblad").Copy
ActiveWorkbook.SaveAs "K:\Persoonlijk\HRM\Public\Verlofplanner\vakantieplannerBreemessheet1.xlsm", FileFormat:=52 "ThisWorkbook.SaveCopyAs".

But I don't know how to extract some lines out of a file and save them into a new file. Maybe you can help ?

Originally Posted by jokkebal

Looks like the system keeps old files in layers in its memory, where I only see the upper layer and all other ones the one below it...

I couldn't say for sure but I'd assume some kind of caching was going on there, yeah

Originally Posted by jokkebal

But I don't know how to extract some lines out of a file and save them into a new file. Maybe you can help ?

I can certainly try, is there any kind of identifier that we could look for in the data? Is it just a certain column (or columns) that the data occupies?

Perhaps we can delete everything that isn't neccessary once we've copied the worksheet and then save it?

In fact, looking at your formula you're only interested in Column I, so you might be able to do something as simple as;

Please Login or Register  to view this content.

Adjust columns to suit your data?

Why not in the code paul suggested copy column I and then paste special as values. then the formula issue is irrelevant

Please Login or Register  to view this content.

Hi Davsth,

Thanks for your support. As a reply to your question :

Because I now realize that copying the entire worksheet as a password-protected file in the public folder is a much easier solution. But that has nothing to do anymore with the initial title of this post, so I will shut this one down and start a new one with that request.
Josse.

Hey Josse

I know you've marked this thread as solved, but I think you're over complicating it a little with the whole protected workbook thing. As davsth suggested you can remove the need for the formula quite easily.

Please Login or Register  to view this content.

Doing that will remove the formulas from Column I and you've no need to worry about it being protected?

It works ! Thanks to both of you.