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.

The ISNA function is an Information Function. It is is used to return information about the status of a cell, or specifically another functions results. The most common use I have for this function is to validate the VLOOKUP function. If the VLOOKUP function is looking for a value that can not be found in the lookup range, then it will return #N/A as the result. Thus it is good to wrap that function in an IF Statement using the ISNA function to replace the #N/A results with something else – e.g. a text string such as “error” or maybe a zero.

=IF(ISNA(VLOOKUP(A1,$B:$B,1,FALSE))=TRUE,"error",VLOOKUP(A1,$B:$B,1,FALSE))

Remember the ISNA function returns a TRUE or FALSE. These equate to a 1 or 0 so can be used in formulas such as array formulas as well.