Hiding your work- Part 1

March 25, 2011 by Leave a comment

What if you want to hide your work?

There are times when you are working on a spreadsheet, when you must create placeholders. Perhaps you are applying some custom formatting to a specific area, but must reference information in another sheet. (For more information on Custom Formatting, see Custom Formatting). You’ll quickly discover that Excel doesn’t allow you to do that. Or maybe you are modeling your business and want to refer to a different sheet and perform different calculations when the month in question is closed and you have actual data than when it is in the future and you are forecasting. An easy way to tell Excel where to look is to put an “A” (for actual) or “F” (for forecast) in the top row. (How you get Excel to automatically determine which to use can be done in a number of different ways, but some functions you might use INDEX, VLOOKUP, IF, or MAX functions; I’ll be sure to fit in a discussion of each of those at some point.)

But you don’t necessarily want the user of the spreadsheet to see this work, right?  Well, you can hide it in one of several ways. You could change the font color to white (the same color as the background), but any user who selected that and other cells, would still see the cell contents. Read more of this post

Advertisement

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , , , , , , , , ,

Custom Number Format

January 16, 2011 by 1 Comment

Figure 1: Write Functions max, min, and average

Do you ever want to include additional information inside of a cell that contains or calculates a number?  Here’s how.  Let’s pretend you have a classroom of students.  You have a list of their Semester Grade Point Averages and want to quickly summarize the class, looking at the highest, lowest, and average.

To accomplish it, you write the formulas to calculate the three figures.  The three functions you’ll use are:
max (), min (), and average () [See Figure 1: Write Functions: max, min, and average]

The next step is to reformat the cell, changing the default or current setting.  Right click on the cell, and select “Format Cells” toward the bottom of the drop-down window.  Once selected, the first tab of the format window, “Number”, will appear.  Choose the “Custom” Category at the bottom.

You’ll then see (to the right) a “Sample” section which shows the result of any custom function you write based on the selected cell’s current contents.  There are several predefined choices, but we’ll write our own.  We can, however, look at this list to get an idea of the different number and date characters (#,$,mm,yyyy) allowed. Read more of this post

Filed under Application (Excel, Access) and Process Development Tagged with , ,