Using Windows security rather than password protecting Excel files for ease of use...

I  have a scenario where a director is going to be viewing summarized data in an Excel workbook from 3-4 other password protected workbooks. I assume s/he will not be able to do that without opening the other files....assuming s/he wants to see the most recent data. What would you recommend? I'm considering recommending that they set up a special group for that drive so that those people in that group can freely access all data within that drive (note the formulas etc will be locked down in the file) but that people outside it can't. Any suggestions? Thanks
LVL 1
agwalshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlanConsultantCommented:
Sounds exactly what I would do.

If probably only drop the file in there once done, and keep the originals elsewhere safe - that way if they trash any formulae then no big deal.

Alan.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
If the viewers do not need to amend  the data then what about a dynamic image of the data?

Data is on a hidden sheet and amendments to the data will reflect in the image automatically.
agwalsh.xlsx
agwalshAuthor Commented:
The viewers do need to input the data for let's say 4 centres in 4 different locations. The director wants to see a summary file of all four locations - s/he only really wants to see the summaries. Each of the sheets will only be open for data entry in specific areas. Everything else is locked down. So a user can view the summary data for his/her centre but the director will have another file pulling the desired stats from all 4 centres. I plan to use tables in the files so even if extra people etc are added in the formulas will be dynamic.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance AnalystCommented:
The Summary file can be viewed by anyone with access to that folder but if the source files are in a different folder to which he/she does not have access then the viewer of the Summary file will not be able to update the links to get the latest data from the source files.

In some instances the formulas may create errors when recalculated.

It is dependent on the method of linking. Examples of formulas between files:

Still work when source file closed
VLOOKUP/HLOOKUP
SUMPRODUCT
SUM of specific range
Reference to specific cell

Does not work when source file closed
SUMIF
SUMIFS
INDIRECT when creating/referring to a link to another file

These are just examples that I am aware of.
Roy CoxGroup Finance ManagerCommented:
If they don't need to manipulate the data then the method that I have used can be adapted for your needs.
Shaun VermaakTechnical SpecialistCommented:
I would link the source workbooks to the master workbook. This way you can use any function and update live or on demand. To be able to see anything other than cached values users will need read rights on these linked workbooks
AlanConsultantCommented:
Splitting points and closing question.

If there are any further queries, please do post back, or start a new question, and I'm happy to (try to) help further.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.