Help save a life - offset, Index/Match or something else?

[Steve]

Need help in automating a financial package compiled in Excel.

We have clients that use QuickBooks for their accounting. We then run
reports and key the most current data into an Income Statement trend tab.
This tab feeds the rest of our package via links. The problem is that we
spend way too much time updating links and chart source data.

Our package is basically:
a tab that compares the most current month with the prior month (side by
side columns)
a tab that compares the most current month with the same month last year
(side by side)
a tab that compares this YTD with the same period for last year (side by side)
a tab full of approximately 8-10 charts

I have learned the Offset function and can make the charts all show the most
current 12 months so that as soon as we key the most current month data that
they all update. So if we had May06-Apr07 from last month then keyed in
May07 data into the trend, the charts would all change to June06-May07. Had
to create a named range for each row to be graphed. Not sure if that was the
best way but it works.

I don't know what the best way to do the other P&L comparison tabs. I
thought I could use Offset but someone else showed me the Index/Match
function. The problem with using that is that I don't want to have to tell
it which columns to grab since they are relatively the same each month (i.e.,
current month vs prior month). I would think that I could tell it in Column
D to always grab the latest month and in column E to take column D less one
month. I need this as automated as possible so that when we key the most
current month in the trend tab that all the other tabs update accordingly.

We have spent the last two years manually updating all of these every month
- you will save my life if you can help me.

Thanks!
-Steve

 

[Steve]

Anyone???

If someone could at least help me with how to do the YTD tab I would be so
far down the path.

I need it so that when I enter in the latest month that it sums the YTD
columns. So if it was April data it would have summed Jan-Apr) but now when
I enter in May that it would sum Jan-May.

Please help...

 

[Roger Govier]

Hi Steve

Assuming you have all of the data entered on a sheet in A1:AK1000, with
a header in row 1 which is the Date with a Custom format of "mmm-yy"
Format>Cells>Number>Custom> mmm-yy
This would cover a period from Jan-06 to Dec-08, but I am assuming there
will be no month entered into this row until you also enter data for the
column that month represents.

The formula
=INDEX($A$1:$AK$4,ROW(A2),MATCH(LOOKUP(99^99,$A$1:$AK1),$A$1:$AK$1,0))

will pick out row 2 values from that sheet for the latest month to have
values.
As you copy down, it will pick up data from successive rows.

=INDEX($A$1:$AK$4,ROW(A2),MATCH(LOOKUP(99^99,$A$1:$AK1),$A$1:$AK$1,0)-1)
will give the values from the month before
=INDEX($A$1:$AK$4,ROW(A2),MATCH(LOOKUP(99^99,$A$1:$AK1),$A$1:$AK$1,0)-12)
will give the values for the month that is 12 months previous.

 

[Steve]

Thanks for the reply Roger. I have tried what you mentioned and it works for
one value at a time. I think I understand how to use offset/index/match to
return one value at a time.

What I really need is to be able to sum multiple values. So if I have a row
of data called "Revenue" and I have data from Jan 06 thru Apr 07 - I want to
sum Jan 07-Apr 07 in one column and compare to a sum of Jan 06-Apr 06 in
another column. Then when I enter data in the May 07 column on my trend
sheet that the two columns on my YTD tab would shift to show Jan 06-May 06
summed in one column compared to Jan 07- May 07 summed in another.

Make sense? I know there has to be a better way...

 

[Roger Govier]

Hi Steve
Then combine it with Offset.
The starting point will be
=INDEX($A$1:$AK$4,ROW(A2),MATCH(LOOKUP(99^99,$A$1:$AK1),$A$1:$AK$1,0)-12)

And starting from there we want
=Sum(???,0,0,1,12)

so put them both together
=SUM(OFFSET(INDEX($A$1:$AK$4,ROW(A2),
MATCH(LOOKUP(99^99,$A$1:$AK1),$A$1:$AK$1,0)-11),0,0,1,12))