A formula that will pickup a new cell and exclude the prior cells

[BrianHall]

Good afternoon,

I'm trying to figure out a formula that will pickup the current month data automatically but at the same time exclude the prior months data. This will be done in a seperate summary tab from the source data tab.

I should mention that this is a monthly report. So every month that I run the report I need the summary tab to pick up the current data souce that I manually enter.

I hate having to point and click the new month data within the summary tab all the time.

Any help would be greatly appriciated.


Thank you....

 

[shyy]

Not sure I understand what you want but some info that might help you out.

To have the cell always use the current month put =MONTH(TODAY()) this will return 11 for November, so next month it should be 12 for December. You don't have to manually update the month just use a SUM with an array.

=SUM(IF(MONTH(H1:H26)=MONTH(TODAY()),I1:I26,0))

Excel 2010
	B	C	D	E	F	G	H	I
1	1488						10-Jan	324
2							11-Jan	423
3							12-Jan	4
4							13-Jan	32
5							11-Nov	42
6							12-Nov	34
7							13-Nov	32
8							14-Nov	4
9							15-Nov	324
10							16-Nov	32
11							17-Nov	343
12							18-Nov	3
13							19-Nov	2
14							20-Nov	2
15							21-Nov	432
16							22-Nov	4
17							23-Nov	234
Sheet10

Cell Formulas
Range	Formula
B1	{=SUM(IF(MONTH(H1:H17)=MONTH(TODAY()),I1:I17,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BrianHall]

Good afternoon Shyy,

What I'm looking for is to have the data in Tab #1 (below) automatically pick up the data in Tab #2 (below) when new monthly data is added.

I hate going back into Tab #1 to change the formula to pick up the current data (month) in Tab #2.

I'm just wondering if there is a way to accomplish this task?

<COLGROUP> <COL style="WIDTH: 48pt" span=3 width=64> <TBODY> </TBODY>
Tab #1 Month MTD YTD Apr 4 7 <COLGROUP> <COL style="WIDTH: 48pt" span=3 width=64> <TBODY> </TBODY>

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

Tab #2
	MTD	YTD
Jan	1	1
Feb	2	3
Mar	3	5
Apr	4	7
May	5	9
Jun	6	11
Jul	7	13
Aug	8	15
Sep	9	17
Oct	10	19
Nov	11	21
Dec	12	23

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

 

[JoeMo]

In tab #1 in the month cell:

=CHOOSE(MONTH(TODAY()),"Jan","Feb","Mar","Apr","May","Jun","Jul""Aug","Sep","Oct","Nov","Dec")

then use vlookups in tab#1 MTD and YTD cells to lookup the month and return MTD and YTD from the table in tab #2.

 

[BrianHall]

Thank you...

 

[JoeMo]

Thank you...

You are welcome - thanks for the reply.