Posts Tagged ‘MS Office’

h1

Finding the size of a Microsoft Office 2007 file

10/01/2010

Prior to Microsoft Office 2007 (MSO2007), office provided a quick and simple way to find out just how big your file was.  This was done by selecting the properties option from the file menu which then displayed the same file properties dialog box that you could select from the file in Windows Explorer.

In MSO2007 the information is still available, but has been annoyingly ‘hidden’ somewhat deeper in the application.  In fact it is so awkwardly hidden that I know many people have not come across how to access it … so I thought I’d share.

Along with the menu item click options I’ve included the accelerator / shortcut keys to help make this as quick as possible.

  1. Select the ‘Office button’ [ALT+F].
  2. Select the ‘Prepare’ menu item [Press E].
  3. Select the ‘Properties’ menu item [Press P].
  4. In the ‘Document Properties’ section that appears, click the title (“Document Properties”) of the section to reveal a drop down list containing just one item – ‘Advanced Properties’.  Select this item to display the file properties.

NB: Please keep in mind that until you save a file, the properties window will not provide a file size though other file properties may be populated.

Advertisements
h1

Get Value from the Text Function in Excel

07/01/2010

I’ve been doing a little bit more work with some spreadsheets recently and I came across a situation where I needed to work with a date and carry out a comparison to a string of text.  My first attempt fell short as it wouldn’t get the value I wanted, but Excel’s TEXT() function came to my rescue.

The issue came about like this.  I wanted to take a date in the format “dd/mm/yyyy” in one cell and display it in a short date format of “mmm-yy” in the cell next to it.

e.g.  “07/01/2010” in cell A1 and “Jan-10” in the neighbouring cell A2.

My original approach was to set the second cell to pick up the value of the first.  So taking the example above A1 would contain “07/01/2010” and cell A2 would contain a formula “=A1”.  I then set the formatting on cell A2 to a custom format “mmm-yy”.

I next wanted to check if cell A2 was equal to “Jan-10”.  This was where the issue arose as although cell A2 displays “Jan-10” its value is actually “07/01/2010″ … the value in cell A1.

The solution was to set the formatting of the A2 cell back to ‘general’ and then change its formula to… =TEXT(A1,”mmm-yy”)

The value of cell A2 is then “Jan-10”.

For me this highlights the difference between the content (what is typed into the cell, i.e. the formula), the value (what the formula evaluates to), and what is displayed (the value can be formatted to change how the user sees it on screen/printed).

h1

Excel: Last Entry

28/08/2009

I was challenged to work out a little problem someone was having with Excel today.  The expectation was that I could write some VBA to resolve it, but once I found out what the task was I quickly managed to put together a purely Excel function based solution.

The task was to pick the last entry from a vertical list of cells.  The list is sequential so there are no blank lines which makes the choice of algorithm to find the last entry quite open.   The algorithm I chose was based on a list length and vector approach.

Example

Example

So to start with I needed to know how many entries were in the list.  The COUNTA function will provide this.  This in effect tells us how many rows we have to step down to get to the last entry.  The OFFSET function allows us to carry out these steps based on the first entry in the list.  Because we’re starting at the first entry then we don’t need to move if there is only one entry, so we need to subtract one from the count to give us the number of “steps” to take from the first cell.

The screen shot illustrates how this is built up for a list in column A starting at cell A1.  The function we would use is simply =OFFSET(A1,COUNTA(A:A)-1,0,1,1)

However this does give us a #REF! if the list has no entries – which looks like an error if you give someone a blank list (essentially because it is).  So if you need to account for this, just include an IF.

=IF(COUNT(A:A)=0,””,OFFSET(A1,COUNTA(A:A)-1,0,1,1))

h1

Excel – Special Names in Dynamic Lists

15/03/2009

I posted earlier about a better way for producing dynamic lists in Excel.  One of the “tricks” I used to simplify things was using the text values of the items in a primary list to identify a secondary list to use.

i.e.

Define the drop down lists

Define the drop down lists

Here We have three lists with the options set as named ranges called “Type”, “Fruit” and “Vegetable”.  Using data validation, drop down lists and Excel’s INDIRECT function means that if “Vegetable” is selected from a drop down linked to the “Type” list as a primary list, a related secondary list drop down will dynamically link to the “Vegetable” named range to validate the drop down.

This eliminates the need to do any complex lookups, but complex lookups look like the option when confronted with a primary list that contains entriess that would be invalid as names for a named range.

For example if we were looking at say UK regions and cities then an entry of “North Yorkshire” in the primary list would cause an issue as it contains a space which is an invalid character in an Excel name.

My solution is to replace the space in the naming of the range with an underscore.  This obviously won’t match to the entry in the primary list but we can use a quick substitution to accommodate this.

Simply put we might have something like in the data validation list entry for a cell linked to cell A2:

=INDIRECT(SUBSTITUTE(A2,” “,”_”))

This would simply replace the spaces with underscores and provide the valid name for Excel to use.

If we’re using the full validation option from my previous post on data validation, then this substitute would be moved into the function of the working area.  So using the same example as the previous post:

Cell D4 would now contain the following function:

=IF(B4=””,””,SUBSTITUTE(B4,” “,”_”))

Cell C4 would (still) contain the following for its data validation list source:

=INDIRECT(D4)

So we can still avoid complicated lookup functions and still keep things relatively simple and instantly readable.