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?

[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="class: xl65, width: 128, bgcolor: yellow, colspan: 2"][TABLE="width: 144"]
<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>[TR]
[TD="class: xl68, width: 192, bgcolor: yellow, colspan: 3"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][TABLE="width: 144"]
<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>[TR]
[TD="class: xl68, width: 192, bgcolor: yellow, colspan: 3"]Tab #1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]Month[/TD]
[TD="class: xl67, bgcolor: transparent"]MTD[/TD]
[TD="class: xl67, bgcolor: transparent"]YTD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"]7[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="width: 144"]
<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>[TR]
[TD="class: xl67, width: 192, bgcolor: yellow, colspan: 3"]Tab #2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]MTD[/TD]
[TD="class: xl66, bgcolor: transparent"]YTD[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Jan[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Feb[/TD]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl65, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mar[/TD]
[TD="class: xl65, bgcolor: transparent"]3[/TD]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Apr[/TD]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]May[/TD]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="class: xl65, bgcolor: transparent"]9[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Jun[/TD]
[TD="class: xl65, bgcolor: transparent"]6[/TD]
[TD="class: xl65, bgcolor: transparent"]11[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Jul[/TD]
[TD="class: xl65, bgcolor: transparent"]7[/TD]
[TD="class: xl65, bgcolor: transparent"]13[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Aug[/TD]
[TD="class: xl65, bgcolor: transparent"]8[/TD]
[TD="class: xl65, bgcolor: transparent"]15[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Sep[/TD]
[TD="class: xl65, bgcolor: transparent"]9[/TD]
[TD="class: xl65, bgcolor: transparent"]17[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Oct[/TD]
[TD="class: xl65, bgcolor: transparent"]10[/TD]
[TD="class: xl65, bgcolor: transparent"]19[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Nov[/TD]
[TD="class: xl65, bgcolor: transparent"]11[/TD]
[TD="class: xl65, bgcolor: transparent"]21[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Dec[/TD]
[TD="class: xl65, bgcolor: transparent"]12[/TD]
[TD="class: xl65, bgcolor: transparent"]23[/TD]
[/TR]
</TBODY>[/TABLE]

 

[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.