Excel - changing workbook formulas for the data to be taken from external Excel file

OK, so I have got a workbook that is collating information from a number of other workbooks. We've got number of users using individual files that feed the information to the workbook (set of Excel files working in a shared networked environment).
For this very reason I need to restrict access to the file (let's call it the global one), for users not to open it to preview values in it as it messes up the reads and writes or the global one. This on its own is working fine.
So in order to enable some people people to see the values (of the global one) I introduced yet another workbook (let's call it a 'visor') that gets its values from the very global one purely to display values stored in the global one.
A 'visor' file I simply copy the global workbook (to retain the look, conditional formatting, etc) and its supposed to have the exact look of the original but take the exact values from the global one.
I then want to change the references in the new file in a simple straightforward way.
I find that
  1. you lose fine text formatting as advanced text formats i.e. where part of text is bold or different size on the original it is not longer in the visor
[/list]you get a lot of zero values even of the original had no zero in them[/list]
  • hence the exercise is much more trouble than I was hoping it to be . This is due to the fact that the original data is not strictly grouped as a rectangle, rather an area that looks like a form with quite a few cells with no value ion them

My question is: has anyone encountered the similar scenario? Is there a way of making a 'visor' out of the original that does not have the caveats I described above? A method of crafting one without having to go through extra actions like copy cell by cell?
I am thinking of something like command that replaces ONLY existing (non empty) cell values with references addressing an external cell for 'own address'?
i.e. where current cell G2 (if non-empty) value gets updated to the following value
='N:\PS\2018\[Global data.xlsx]ANNUAL'!G2

What I need to mention is that there are no linked files currently in the visor file , no references that can be replaced. (In my attempt I am looking to sort of connect the values from external file but only for cells that DO HAVE a value)
DominicAsked:
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.

NorieAnalyst Assistant Commented:
Dominic

Even with formulas that reference ranges/cells in other worksheets in the same workbook you will lose formatting, and not just what you call 'fine' formatting.

Also, if there are blank cells in the data you are pulling over they will generally come over as 0.
Ejgil HedegaardCommented:
Use this to eliminate zeros for cells with no value
=IF('N:\PS\2018\[Global data.xlsx]ANNUAL'!G2="";"";'N:\PS\2018\[Global data.xlsx]ANNUAL'!G2)

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
DominicAuthor Commented:
Thank you all, it seems a conditional check and return if non-blank value seems good way forward although I was hoping Excel itself can offer more in respect of retrieving data from an external source.
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
Microsoft Excel

From novice to tech pro — start learning today.