Formulas and Functions

[Soundra04]

Hello, 

I am trying to build a simple Profit and Loss account statement with one tab to be total for the year with another tab on the back end with individual months. How would I get the total for the year by typing the month on the front end of the tab? Meaning if I type March on the front tab, I need the back end to total January to March figures and post to the year to date on the front end.

Is it to do with name range and formulas?

[#2]

@Soundra04 

Facilitate testing, please attach your sample Excel file with your manually entered results.

[#3]

Thanks a lot.Please find enclosed hereto my worksheet

[#4]

@Soundra04 

In the attached file, the dynamic sum formula in B4 is: 

=SUM(INDEX(YTDdata,ROW()-2,1):
INDEX(YTDdata,ROW()-2,
MATCH(B$2,YTDlabels,0)))

Copy down the foregoing formula to the proper cells. Assuming you typed, 3/1/2019 in B2, the Total Revenue will be as shown below: 

[#5]

@Soundra04 

Thanks a lot for your help. It is absolutely fantastic.

Is it coming under Dynamic Sum section?

If there is anything more I will certainly contact you.

You seems to be an expert.

Kind Regards

[#6]

You’re very much welcome!

[#7]

Hello

Sorry for troubling you. Can you let me know as to how I could include the corresponding period for the previous year when I input 1/9/2019 to calculate and post the last year's figure in the next column.

I would greatly appreciate your help.

Kind Regards

Soundra

[#8]

When I type the current month I need the previous year's month also to be highlighted.

Thanks 

[#9]

Hi Robert,

Please find enclosed hereto the updated file. Could you also let me know where the tables sit for the formulae.

Kind Regards

Soundra

[#10]

@Soundra04 

In the attached version of the same file, the dynamic sum formula in C4 is: 

=SUM(INDEX(PYTDdata,ROW()-2,1):
INDEX(PYTDdata,ROW()-2,
MATCH(C$2,PYTDlabels,0)))

When copied down to the proper cells, the Total Revenue will be as shown below: 

When you press Ctrl+F3, CYTDdata refers to ='Year todate Profit and Loss '!$B$3:$N$26, as shown below: 

Note that "CY" and "PY" pertains to Current Year and Prior Year, respectively. 

[#11]

Thanks a lot Robert.

I got it.

God Bless you abundantly

Soundra 

[#12]

@Twifoo 

Hello Robert,

I do not know whether you were able to go through my request in the forum.

I shall appreciate if you would kindly help me get some formulae set in for Family Cash Flow to be shared with my family folks.

What I want is when you enter the date or month in cell B4 in the first tab, this should point to the month typed in B4 to be reflected in the second tab month column. Upon this third tab to pick up the total for each row automatically and post onto the respective colum in the 2nd tab. This to be repeated for every time a month is typed in cell B4 .

I shall be grateful, if you could do this favour for me.

Thanks and Kind Regards

Soundra