Spy Journal 3.0 Filtering the web for you

This is a “real” blog post. Doesn’t feel that way yet? That’s cos I am still rambling. Or is that why it is a real blog post? Maybe this has something to do with the fact that it is the absolutely unheard hour of 6:30am and I was awake at 5:30 and have consumed a very nice coffee and am buzzing!

So lets deal with the facts involved here. First off the screen door slamming about 3 metres from head while I was asleep and in the middle of a strange confused dream where I was involved in counter terrorist warfare including a shootout in a barn where I stopped bullets matrix slow motion style using a leather cushion and then encourage the last remaining terrorist to shoot himself so I could rescue the civilian hostage (predictably a woman) rudely awoke me. Particularly as that was followed up with scratching sounds on the door. I went to the door with my big stick (kept beside my bed) and found a gecko on the inside: conclusion neighbours cat (that I daydream about sending to cat heaven) was chasing the gecko and leaped at the door slamming it shut.

Security vulnerabilities were announced today in Microsoft Excel and also in Drupal.

See the Official MS security advisory (968272), and the explanation by ars technica.

Our advice for now would be to be EXTREMELY careful when opening any Excel spreadsheet, Word document or PowerPoint file that is emailed to you. Rule of thumb – if you don’t know the person who set you a file (any file) NEVER open it. If you weren’t expecting the file from someone you do know then email or call them and ask them if they sent you it and if so what it is BEFORE opening it. I trust that MS will release the appropriate patch shortly.

The Drupal security vulnerabilities have been announced on the Drupal website and upgrades and patches released for both version 5 and 6.

For our Jethro Consultants Web hosting customers this means an immediate patch for those who are on maintenance contracts with us, and for those who are not they pay us by the hour to apply. We get to those as soon as we can. We are also busy upgrading our own websites.

I had a colleague ask me this question today. (edited to protect the innocent – you know who you are!)

I copy data out of E_____ (am sure you have heard of it), and paste it into an excel spreadsheet.  I have a couple of pivot tables hanging off the back of this data.  As this is project data, the longer the project goes on, the larger the dataset gets.  Is there any way in VBA that I can get the pivot table to auto-expand the data set that it is using? 

Here is my answer.

Hey W__

There is a simple solution that doesn’t require any vba at all

First you will need to make a dynamic range name that covers the data being pasted.

When I wrote the how to use VLOOKUP and HLOOKUP article a few weeks ago I hinted at writing how to use the COLUMN, ROW, MATCH, INDEX and OFFSET functions to enhance the use of the VLOOKUP and HLOOKUP functions. Today we will look at the use of MATCH.

First of all here is a common scenario.

You create a table of say 4 columns, and then in another sheet you create a VLOOKUP function that returns results from the 4th column. EG =VLOOKUP(lookup_value,table_range,4,FALSE) where the 4 relates to the 4th column. This works great until one day you (or your colleagues) delete the 3rd column in the table, or insert another column in the middle of the table. Now your VLOOKUP fails as the 4th column either doesn’t exist, or is now actually the 5th column. In this case the VLOOKUP formula you created is just not flexible enough to handle the changes.

The following explanation builds on the dynamic range name process defined in my article written back in 2004. You will need to create a dynamic range name (in this example called table_header) that works on the header row of the table you are performing the VLOOKUP on.

In Excel 2007 Use the Name Manager from the Formula tab. In Excel 2003 and earlier use Edit Insert Name Define. In both versions CTRL F3 is the keyboard short cut.