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
- 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
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)