Comparing Year on Year Data

[mick02]

Hey folks, I was wondering if anyone could help me out on this on. I've got an Excel sheet with data from different departments of the company I work for on separate worksheets. I'm trying to create an overview sheet that will compare last years figures with this years figures. The problem that I'm having is that when the excel sheet is populated that the figures in the overview page ONLY show the difference between the months to date i.e. If the figures for Jan, Feb, March and April are populated then it will only compare the data between Jan, Feb, March and April of 2007 with the figures for the same month in 2008.

Another problem that I am faced with is that obviously this document is going to be dynamic and that year after year it will change. Is it easy to take this into account or is it a case that the rules and formulas will need to be changed each year?

I hope I've explained this properly. Hopefully the attached Excel sheet will explain it. Thanks in advance for anyone that can help out.

Mick

[AndySuk]

Hi Mick

Are you sure you want to do this progromatically? Looking at your worksheet it LOOKS as though you can just use links and lookups to do what you want.

As you have the year in each column then you could use HLOOKUP to look up the year on the correct sheet. For example for Ireland your lookup range would be B4 to L20. Use the "2008" header as the criteria for the lookup and ask it to return row 17 so the basic formula on your overview sheet is hlookup("2008",b4:l20,17,false)...you'd need to add in the sheet names and ensure the ranges are right etc etc.

Use this method and, if you used a link to your header cells (ie the cell address for 2007/2008/2009 etc) then it would be pretty dynamic although you'd probably have to change the ranges from year to year.

I suspect I may have missed something but can't see the need for programming from your sheet at the mo so do expand a bit if so. See if the attached works for you.

A

[mick02]

Hey Andy,

Thanks for the reply, it looks as though a hlookup will do exactly what I'm looking for. I've a couple of questions. You say return row 17? Why 17 or am I just being thick?

P.S. I liked the name you gave for the French range

[AndySuk]

:-) ...I was in non-PC mode!

I think it may be that I've confused things by naming the range...when the lookup is looking for Row 17 it's not Row 17 on the TAB - it's the 17th row in the lookup range. So Excel has looked for the criteria "2008" in the range B4 to L20 (which I've renamed ire_range). Go down 17 rows form the first one in the range (the first row always has to have the values that you're using as criteria) and you get to the total row which is the one from which you want the values returned. The 17 is a count from the first row in the lookup range - not the numbered row from the Excel sheet.

Hope that makes a bit more sense but shout if not.

A