Sum Date Based On Date

[soccerdude44]

I am trying to get my 1st sheet to be a summary of my other 5 sheets that I have within my workbook. Basically what I have done is created a spreadsheet for each type of investment account I have. Within each sheet I have put what I want my projected monthly contribution to be along with my expected average rate of return. This has given me an amount that I have as my goal that I want in each account at the end of each month. I then have a column for the actual amount I have at the end of each month. And then another column that shows the difference between the actual and goal amounts so I know how far ahead or behind I am. I have extended it down through the age of 65 on a month by month basis. What I would then like to do is have the current month difference be shown on the summary sheet (the first page) for all 5 accounts. So in the perfect world my summary sheet would have 5 different rows, one for each account, that shows the difference for each account but that can change which cell it is looking at for the difference based on what month it is without me having to manually update at the end of each month. Does that make sense?? Let me know if you have any questions.

Thanks For Your Help!

 

[RonB1111]

Assume:
1. One sheet is named "Investment1" for one of your investment accounts.
2. Col A lists the last day of each month in Excel date format
3. Col D is the difference between actual and goal at the end of each month

Try this formula on one row of the summary sheet and then modify it by changing the sheet name to "Investment2", etc (or whatever your sheet names are):

=INDEX(Investment1!D:D,MATCH(EOMONTH(TODAY(),-1),Investment1!A:A,0))

 

[soccerdude44]

Your a genius!! That is exactly what I was looking for! Just out of curiosity did you know that off the top or your head??? If so how did you learn so much? I would love to become an Excel Wizard!!

Thank!!

 

[RonB1111]

You're welcome!

I learned Excel by using it for many years and using books & google to figure out approaches to solve problems. Searching this forum is also extremely helpful in learning new Excel techniques This Mr Excel website has many resources that can help. If you haven't done so already, try to understand the formula I gave by googling "Excel Index/Match" and "Excel eomonth" and "Excel today()". If you have questions, don't hesitate to ask.