Hidden workbooks and colour schemes

[floricita]

Hi there.

I was wondering about somethings in excel.

1. I was sent a locked spreadsheet the other day and some things which I hadn't seen before were included. there seemed to be another "layer" behind this spreadsheet, which if you changed a drop down box, changed the populated figures. I was wondering how this mechanic worked?

I did an alt f8 to see if ity was a macro but no macros showed up in the work book, but it does look like some sort of sub total application has been used.

Just wondered how these "layered spreadhseets work and are contstructed in principle?

2. My other question completely unrelated is. What colour schemes work best in spreadsheets? Are there any guidelines at all? I only ask because the content I provide for the puroposes required is very good, but my presentation of colour is just terrible and I wondered how I can improve it.

Thanks

[Whizbang]

Can you provide a sample workbook? That way someone can explain exactly how it was done in this instance.

In general, I tend to create a hidden "system" sheet that holds my lists and various behind-the-scenes formulas that the end user doesn't need to see. Then I'll have my "working" sheet that has all the data input or reporting or whatever that is clean and purpose oriented.

[floricita]

Thanks.

Unfortunately I can't post a sample, because it's locked and the entire worksheet is sensitive.

How do you create the hidden worksheet that the end user doesn't need to see? This is very interesting.

[Whizbang]

Attached is just a simple example. When you open, you'll see you have the option to select the name and number. This will then search the data and give you a count of that name/number combination.

There are to things hidden in the system sheet. 1 is the lists used in the "Select Name" and "Select Number" options. The second is the criteria for the DCOUNT formula. These two items are in the System sheet so that they do not clutter up the Data sheet.

Go to "Format" -> "Sheet" -> "Unhide" and select the System sheet. You will then see that columns A and B have the lists, and then range D1:E2 has the DCOUNT criteria.

Again this is just a simple example and can be changed to suit your needs.

The main thing when making an excel workbook is to evaluate the purpose of the workbook and who will be using it. It is then best to design the workbook for ease of use and purpose. Do not info-dump the user. If they don't need to see certain data or whatnot, don't show them. This is not because you can't trust them with the data (although this is often the case), but simply because they have a certain task to do, and it will make it much easier for them if they don't need to sort through gobs and gobs of data to find it.

For that reason, it is usually best to seperate "System" stuff that is used to calculate and organize info or user input. Likewise, it is often best to seperate the data from the reporting of said date. Usually when you report info, you are pulling only certain bits of data from the larger database. Displaying a whole table of data and calling it a report is not effective. By controlling what the user sees and inputs, you reduce errors and increase effectiveness.

[floricita]

Thanks. That is very helpful.

I was just wondering how you managed to filter the select name and select number using data from the hidden worksheet?

[Whizbang]

Are you talking about the Data Validation drop-down lists?

If so, then you can use ranges from other sheets in Data Validation, as long as those ranges are named. Do a search for "Excel Named Ranges" for more info.

If not, are you talking about the DCount formula?