Managing large speadsheets

[PeteC]

At my Primary school we maintain a spreadsheet that tracks each cohort
of 45 children from their Reception year through to Year 6. The sheet
includes a wealth of information ranging from Name, Age, Gender,
Ethnicity, etc... through to SAT and other test results. Altogether,
there are some 40-50 columns.

The sheet contains so much data that it makes its use tedious in the
extreme and I intend to make it easier to access. I want to create
separate tabbed worksheets (in effect, different 'views') that are
linked to the main document such that each year group's current year
data can be viewed on its own.

I've copied and pasted data using Edit/Paste Special/Paste Link and this
gives me information that is linked to the master document and which
updates when changes are made to the source. So far so good, but the
info copies across with a zero in every empty cell whereas the source
cells are just empty; is there a way to suppress this effect? I'm
currently using conditional formatting to make the zeros the same font
colour as the background which works well enough, but I just wonder if
there's any other way of dealing with this?

Additionally, and maybe more importantly: Is there a way to allow data
to be entered both from the source AND target worksheets and have it
appear in either view? This would mean that staff wouldn't have to
struggle with the aforementioned 'monster' worksheet and could enter
information in whatever view they happened to be working on. Similarly,
is it possible to sort data in any view and have the effect appear in
every linked sheet?

TIA,
Pete

 

[Ragdyer]

If I understand you correctly, this "monster" sheet is already in existence,
and you are now attempting to introduce some sort of manageability into it.

I believe that perhaps the best approach for this might be what you may have
inadvertently referred to ... views ... which in XL is called "Custom
Views".

I have used this feature on several of my large WBs, and found it to be very
useful, AND very easy to set up.
In fact, you can have a "views" box added to your tool bar, showing the
current view in effect, and allowing you to access a drop down menu of all
the other existing views, each of which can be immediately displayed with a
simple click of the mouse.

This approach eliminates the need of all the mechanizations that you're
attempting to accomplish here.

If you have any interest in exploring this procedure, this old post has some
information:

http://tinyurl.com/3h9s3

Post back if you have any questions on this feature.

AND, if you wish to continue in your current project,
To eliminate the display of zeroes, try this:

Where your original formula might be,
=Sheet1!B21
Adjust it to,
=IF(Sheet1!B21="","",Sheet1!B21)

 

[PC]

RD,

I appreciate the help you've given and yes, I think you've cracked it with
the 'Views' solution.

The other fix, that deals with the appearance of zeros in what I'd like to
be empty cells, is also appreciated.

Sorry I'm late in thanking you.

Regards,

Pete

 

[RagDyer]

You're welcome, and thank you for the feed-back.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

RD,

I appreciate the help you've given and yes, I think you've cracked it with
the 'Views' solution.

The other fix, that deals with the appearance of zeros in what I'd like to
be empty cells, is also appreciated.

Sorry I'm late in thanking you.

Regards,

Pete