"Dan Brimley" wrote...
....
There are other people who need some of the information on one tab
of the worksheet, but they are not allowed access to the file. I
have been asked to come up with a way to make this happen.

And I can't be accomplished by just allowing access to the one tab
they need. They can have NO access to open the file at all.
....

That so, there's NO way to do this without saving only the data these
nonprivileged users need in a separate file they can access.

That is, they can't get ANYTHING from any file for which they lack
operating system READ permission, but if they have OS read permission
to an .XLS file, they can open it in Excel.

I thought of creating a separate worksheet that links to the info
they need, but the problem with that is that there is a lookup on
the tab they need access to, where they put in an ID number and it
populates the other fields on that worksheet from other worksheets
in the file. So if they had a linked worksheet, how would it pass
the ID number to the original file to populate the fields?

This makes it a practical impossibility. Excel doesn't provide this
kind of security/protection. If your nonprivileged users need access
to sensitive data in the sensitive workbook, they need to be able to
open the workbook. If they can open the workbook, Excel provides no
robust protection to prevent them from unprotecting the workbook or
any worksheets.

What you need for this sort of thing is a database running on a server
that would allow nonprivileged users to run stored procedures that
would run queries against tables of sensitive data using different,
broader permissions than the nonprivileged users possess. The
nonprivileged users would see the query results but would have no
direct access permissions to the tables.

It MIGHT be possible to hack something like this using Excel Services,
but I don't have any idea how to do that. If you're not an IT
department developer, this may be something you can't do yourself, but
it might be something your IT department could do.