# Posts for 11/2008

## Excel 2007 - Ribbon survey

This is the first of it's kind I've come across: an interesting survey examining users' experiences with the new ribbon GUI in Office 2007.

No comment :-)

ps. but a must read for all those doing Training Courses

## How to find the previous or last filled cell in a column in an Excel spreadsheet?

During an Excel course yesterday, someone wondered whether it was possible to fill out a column with ascending numbers, whilst omitting the blank lines (or lines where the triggering value in a column are blank). I'm including an image here to kind of clarify the nature of the question.

My initial wanderings brought me to some interesting approaches based on the use of following formula based on what I found at this site:

=MAX(IF(LEN(A1:A100)>0;ROW(A1:A100);0)) - to find the row number of the last filled cell in a column

=INDEX(A1:A100;MAX(IF(LEN(A1:A100)>0;ROW(A1:A100);0));1) - to get the content of last filled cell in a column

These are array formulas of course, to be entered with Ctrl-Shft + Enter; also bear in mind you that you need to use the commas or semicolons to separate the arguments depending on the regional settings you use.

Finding the content of the last filled cell in a column, could also be done with something like this, using the ADDRESS and INDIRECT functions:

=INDIRECT(ADDRESS(MAX((ROW(1:50)* (C1:C50<>"")));COLUMN(C:C)))

These formulas only consider the last cell of the column however, and to really solve the uttered puzzle we need to obtain the last cell of each previous block of cells, in order to be able to continue and increment the counting. So this is what I came up with :

=IF(F3<>"";IF(A2="";ROW()-COUNTBLANK(OFFSET($A$2;0;0;ROW();1))-1;A2+1);"")

If there's nothing in the triggering cell, then of course there's no numbering. Then if there's something in the previous cell, the numbering can just go on (+1). However when the cell is empty, in stead of looking for the last cell of the previous block, I finally decided to just go and get the number of blank cells and reduce it from the row number. That does the trick.

But yes if you have another approach or know a way to get the last cell of the previous block of cells to increment it, I'd be delighted to know !

## Managing external links in Office 2007

If you're used to use the Paste Link functionality between PowerPoint and Excel, or similar functionality to obtain links between your different Office applications in Office 2007, then at one point you'll be wondering where the management tool went...

well you know: the dialog box which in older versions was available from Edit | Links...

Well very simple : it now sits under the Office button | Prepare | Edit links to files

Gosh... what a location I hear you thinking - yes I did so too :-)

ps : If you haven't done so, it may be a good idea to download the Reference Guides to find locations of 2003 commands in 2007.## Transferring the Quick Access Toolbar settings in Office 2007

The easiest way to transfer the Quick Access Toolbar from one computer to another is simply copying the *.qat files in your Application Data folder.

You'll find these here:

- XP: C:\Documents and Settings\'username'\Local Settings\Application Data\Microsoft\Office\*.qat
- Vista: C:\Users\'username'\AppData\Local\Microsoft\Office\*.qat

Simply copy the *.qat files for the customized quick access toolbar you're after from one computer to another.

Ps : found this one here:http://www.avianwaves.com/Blog/default.aspx?id=74

[Read more on Transferring the Quick Access Toolbar settings in Office 2007...]## Calculate variable cumulative rates and commissions in Excel

Doing courses has the interesting side-effect that people come with all kind of questions challenging you to go beyond what you know and are able to do all the time.

So next situation was proposed by one of my participants, and although I had some initial pathways to solve the riddle, I really started wondering about better and other logics and ways to approach the question.

Suppose you have a salesperson having sold 45000 € - a cumulative bonus needs to be calculated based on a table, considering the proportional bonus for each scaling :

0 | 0,00% |

20000 | 2,50% |

30000 | 5,00% |

40000 | 7,50% |

50000 | 10,00% |

60000 | 12,50% |

70000 | 15,00% |

80000 | 17,50% |

So in other words: (2,5% * 10000) + (5% * 10000) + (7,5% * 5000) i.e.

upto 20000 there's no bonus, below 30000, there's 2,5%, below 40000 there's 5% etc. (the vlookup logic)

So I published this question on an outstanding Excel discussion forum and obtained plenty of inspirations through it.

One of the links pointed to a very broadening approach for me: http://www.mcgimpsey.com/excel/variablerate.html.

The most simple solutions to me finally seemed these:

In the range A1:A8 enter the numbers 0,20000,30000,40000,50000,60000,70000,80000 B1: 0 B2: =(A2-A1)*C1+B1 Copy from B2 to B8 C1:C8 - enter the percentages 0%, 2,5%...17,5% E1: the amount being examined: 45.000

And for the formulas as such

=VLOOKUP(E1;A1:B8;2;TRUE)+(E1-VLOOKUP(E1;A1:A8;1;TRUE))*VLOOKUP(E1;A1:C8;3;TRUE)

=SUMPRODUCT(--(B11>$A$1:$A$8); (B11-$A$1:$A$8); $D$1:$D$8)

## Criteria ranges with functions in Excel

Yes you can use functions in Excel's criteria range when using the advanced filter.

Here's a sample.

Remark that in this scenario, you don't need to have something written in the criteria range's label cel; but you do need to select.

Another thing is that you need to refer to the first cell with content (below the label) to refer to a column in your expression.

Finally you need to think carefully about absolute and relative references - as always actually :-) ; the result of the function may give a 'FALSE' or a 'TRUE', even an error code, but the filtering will work.

## Excel Resources and Links

### Some interesting Excel links :

- http://www.mcgimpsey.com/excel/index.html
- http://www.thecodecage.com/forumz/worksheet-functions/
- http://www.eggheadcafe.com/conversationgroups.aspx?groupsummaryid=11
- http://www.simple-excel.com/
- http://www.contextures.com/tiptech.html
- http://www.mrexcel.com/
- http://blogs.msdn.com/excel/
- http://www.ozgrid.com/Excel/
- http://office.microsoft.com/fr-fr/training/CR100479681036.aspx
- http://en.allexperts.com/q/Excel-1059/
- http://andypope.info/
- http://www.exceltip.com/
- http://spreadsheetpage.com/
- http://www.exceluser.com/
- http://www.cpearson.com/Excel/Topic.aspx
- http://www.jkp-ads.com/Articles/ExcelArticles.asp
- http://excelusergroup.org/forums/
- http://spreadsheetpage.com/index.php/tips
- http://www.youtube.com/ExcelIsFun
- http://www.meadinkent.co.uk/excel.htm
- http://peltiertech.com/Excel/ChartsHowTo/index.html
- http://office.microsoft.com/en-ca/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx - working with structured references
- http://www.mvps.org/dmcritchie/excel/excel.htm - an ocean of stuff by MVP David McRitchie
- http://chandoo.org/wp/ - Excellent blog site by a passionate excel evangelist Purna Duggirala
- http://excelribbon.tips.net/
- http://members.chello.nl/jvolk/keepitcool/download.html - Translation tool for functions (older excel versions)
- http://www.atlaspm.com/xat/ - Excel Aptitude Test
- http://excelhelp.tech.officelive.com/

### Specifically linked to VBA :

- http://www.excel-vba.com/excel-vba-solutions-beginners.htm
- http://www.xlmacros.com/
- http://www.cpearson.com/excel/Events.aspx
- http://www.cpearson.com/excel/AppEvent.aspx
- http://www.techonthenet.com/excel/formulas/index_vba.php
- http://www.dailydoseofexcel.com/
- http://excelvbamacro.com/
- http://www.anthony-vba.kefra.com/
- http://bytes.com/topic/access/insights/648856-debugging-vba
- http://www.ozgrid.com/VBA/
- http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=302
- http://www.contextures.com/xlfaqmac.html#LoopCells
- http://www.exceltip.com/vbatips.html
- http://www.rondebruin.nl/tips.htm
- http://akoul.blogspot.com/
- http://www.excelvbamacros.com/
- http://excelpoweruser.wordpress.com

### For those users who feel they need to practice more:

- http://www.abacustraining.biz/ExcelExercises.htm: some basic exercises
- http://www.jegsworks.com/Lessons/numbers/index.html: lessons with exercises
- http://web.utk.edu/~dhouston/excel/exercise.html: College exercises from David J. Houston
- http://www.internet4classrooms.com/on-line_excel.htm: Excel practical tutorials
- http://webpages.dcu.ie/~kennedal/ExcelExercises/SampleExcelExercises.htm: exercises from easy to less easy :-)

### Some hints towards books

- http://www.contextures.com/xlbooks.html
- http://www.dailydoseofexcel.com/daily-dose-of-excel-book-list/
- http://www.amazon.com/Excel-Tools-few-good-books/lm/R2BKQ03V2LL00J
- http://eu.wiley.com/WileyCDA/Section/id-305634.html

### In french:

li/lihttp://www.excel-exercise.com/## Blocked Outlook attachments

To solve problems with Outlook 2007 attachments which are blocked:

- 1. Make sure Outlook is closed.
- 2. Open your registry editor by opening the Run command and type regedit
- 3. Locate the following key

Outlook 2000 [HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Outlook\Security]

Outlook 2002 [HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Outlook\Security]

Outlook 2003 [HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Security]

Outlook 2007 [HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Security] - 4. Go to Edit-> New-> String Value and name the value Level1Remove (case sensitive!)
- 5. Double-click on the newly created value and enter the extension including the “dot” that you want to open in Outlook. For instance .exe

If you need to enter more than one extension you’ll have to type separate them by a semicolon like this; .exe;.bat;.url - 6. Press OK on the input box and close the registry editor
- 7. When you open Outlook the attachments which hold those extensions aren’t blocked by Outlook anymore.