I have a lot to say and remember about excel

today

for filling in blanks with the content of the cell (in this case above)

choose a range of items you want to fill in the blanks with the cell above

use f5 to display find

click on special

then choose blank

type the + sign then press the up arrow (choosing the cell above)

then press Control / and Enter at the same time

if you are going to sort or do anything to change the positions of the items you might want to select the area (range) again and copy the whole thing and right click and choose paste special and choose values (this pastes just the value back into the range selected)

 

fill down blanks (from a copy of a pivot table) (a favorite) but not easily remembered.

  • hold - ctrl+G, alt+S, K and then enter
  • Ctrl G opens GoTo dialog
  • alt+S selects "Special" from dialog box
  • k to pick blanks  Enter or click OK  (now all cells that were blank are selected)
  • begin a formula by typing the equal sign. Then Up arrow. Hold down Ctrl and hit enter.   Equals up arror creates a formula that makes the cell the same as the one above it.

copy page formatting from one sheet to another

  1. select the tab that has the sheet that has the print setting you want to copy.
  2. Select the other tabs you want print settings copied to. Press Ctrl and click on the other tabs.
  3. Select File, Page Setup and click OK
  4. I don't think you can undo this.

get a list of file names from a directory into excel

source http://j-walk.com/ss/excel/usertips/tip077.htm

but the simple open comand prompt -

type the dir command and copy (with mouse) the directory list you need, if there are spaces in the name put the whole thing in quotes.  then type >c:\filelist.txt after it - this will output the info into a text file that you can import into excel

 

For the oh-so lucky of you who have to migrate to Excel 2007

you may have run into a problem with sumif (or countif of countblank) .. it doesn't save values so when you email a file .. it has value error messages

the funny part is - according to Msft it never did http://support.microsoft.com/kb/260415

so anyway the fix is to re-write all sumifs with conditional Sum(if formulas as in:

 

NOTE: You must enter each formula as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER.

SUMIF

Instead of using a formula that is similar to the following

=SUMIF([Source]Sheet1!$A$1:$A$8,"a",[Source]Sheet1!$B$1:$B$8)

use the following formula:

=SUM(IF([Source]Sheet1!$A$1:$A$8="a",[Source]Sheet1!$B$1:$B$8,0))
 
Print directory listing instructions / http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623 modify this to edit it in Excel (make a check list of files within a directory)  - here is the modification for the bat file:
 
@echo off
   dir %1 /-p /o:gn > "%temp%\Listing"
   start /w excel "%temp%\Listing"
   exit
 
using the registry info in the link  (above) this makes a right click choice to save a list of a folders contents to a file named listing.txt which opens in excel
 
note in XP this works .. these are vista directions: http://www.watchingthenet.com/how-to-print-a-directory-tree-from-windows-explorer.html