• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rolling income statement from different tab

T

thekingofnoo

New Member
So, essentially I have an income statement on one tab and want to develop a rolling TTM on another tab. In the tab I want to have the TTM numbers, I have my months laid out in Row 7 (B7 specifically) with the line items beginning in line B10. On the other tab, my months start on AV7 and the first line item is AV10. On the same tab, there is a gap from BB to BG where there is year-end and audit information I do not want returned.


What I have done already is created a drop-down so that I can choose the month I want and the data rolls forward. I can't even begin to understand how to get the data from the original tab to my rolling TTM one. I tried this formula for B10:


=OFFSET('Detailed Income Stmt BRHAS'!AV10,0,COUNT('Detailed Income Stmt BRHAS'!AV10:BA10,'Detailed Income Stmt BRHAS'!BH10:BS10-1,1,1))


Somewhere someone said that would do it, but it returns a value from BD10. I was thinking of developing an =IF function that says if the value in B7 is a value from range AV7-BS7 (BS 7 is where i want to end it), but only one that is in a date form and then choose the lowest month and then return the value from the lowest month....but honestly i have no idea if that is even the rght way to go about it.


Btw this site is awesome for learning Excel users. Thanks to anyone and everyone that gives this a shot.
 
=OFFSET($a$7,,1,1,12)


Then just drag along for 12 cells

Then connect a drop down list of months to the first "1" in the formula. This will increase where the 12 months start.


Enjoy.
 
oh. You have to enter it as an array formula. So Press ctrl+shift+enter

Sorry I forgot to tell you that ;)
 
You must log in or register to reply here.
Back
Top