Spy Journal 3.0 Filtering the web for you

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.

Music tracks only listed here. Screenshots taken from Media Player with Microsoft OneNote and pasted into Windows Live Writer and then published to a Drupal 6 website.

There has been a lot of negative press about Windows Vista, mostly undeserved, though some of the initial problems with Vista like file copy speed were a problem. However in general the Vista Operating System is a vast improvement of Windows XP in a number of key areas; Security and patching , system stability, application and driver support, plug and play and networking, user interface, and features.

One of the most common things I hear about Vista is that it doesn’t work as well or is not as good as Windows XP. In every case so far I have been able to educate the people saying this and show them just why it is better than XP in these key areas. In every case with a little hands on user training and demonstration of the killer features (like search and virtual folders), the security and system stability, wins these people over. Slowly I am converting all my clients and friends to Vista.

I can absolutely state that most of my support calls from clients originate with Windows XP machines. Almost without exception my Vista clients do not call for operating system or application issues. As a business supporting hardware installations, I much prefer selling Vista to XP, as I know there will be less issues. I now refuse to install XP on a clients machine and recommend a hardware upgrade to support Vista.

Read More

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.