Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help save a life - offset, Index/Match or something else?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help save a life - offset, Index/Match or something else?
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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help save a life - offset, Index/Match or something else?
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" FormatCellsNumberCustom 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. -- Regards Roger Govier "Steve" wrote in message ... 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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help save a life - offset, Index/Match or something else?
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" wrote: 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" FormatCellsNumberCustom 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. -- Regards Roger Govier "Steve" wrote in message ... 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... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help save a life - offset, Index/Match or something else?
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)) -- Regards Roger Govier "Steve" wrote in message ... 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" wrote: 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" FormatCellsNumberCustom 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. -- Regards Roger Govier "Steve" wrote in message ... 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Offset/Index/Match formula | Excel Worksheet Functions | |||
index match offset? | Excel Worksheet Functions | |||
Index - Offset - Match Issues | Excel Worksheet Functions | |||
Index, Match, Offset? Not sure which to use | Excel Worksheet Functions | |||
lookup, index, match, offset, etc. | Excel Worksheet Functions |