Return Latest Month Value

[kumara_faith]

Hi,

I have the following tables;

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	Table 1														Table 2	Tuesday, 31 January, 2023
3	Branch	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Branch	Sales
4	Texas	300													Texas	100
5	New York	300													New York	100
6	Chicago	300													Chicago	100
7	Total	900													Texas	100
8															New York	100
9															Chicago	100
10															Texas	100
11	Table 3														New York	100
12	Total	900													Chicago	100
Sheet1

Cell Formulas
Range	Formula
D4:D6	D4	=SUMIF($Q4:$Q12,C4,R4:R12)
D7	D7	=SUM(D4:D6)
D12	D12	=LOOKUP(2,1/(D7:O7>0),D7:O7)

Table 2 has the raw data. This data is updated daily.
Table 1 is the summary from Table 2

In Table 3, I am trying to take the total for the latest date. Example if today is Jan 31, then the total will show 900. However, when the date changes to Feb 1 2023 in cell R2,, the total in Table 1 which is based from Table 2 will change as well. Assuming today is Feb 1 2023, an example of the new tables will look as below:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	Table 1														Table 2	Tuesday, 31 January, 2023
3	Branch	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Branch	Sales
4	Texas	300	700												Texas	100
5	New York	300	300												New York	100
6	Chicago	300	300												Chicago	100
7	Total	900	1300												Texas	500
8															New York	100
9															Chicago	100
10															Texas	100
11	Table 3														New York	100
12	Total	1300													Chicago	100
Sheet1

Cell Formulas
Range	Formula
D7:E7	D7	=SUM(D4:D6)
D12	D12	=LOOKUP(2,1/(D7:O7>0),D7:O7)

Is there a way to build a formula in Table 1 that takes the sum from Table 2 only for the respective months based on the dates in cell R2 and the figures in Table 2?

 

[kumara_faith]

Hi,

Thank you for all the suggestions. Just to confirm, there is no way we can retain the previous month data as the current date moves to the next month ?

 

[kumara_faith]

Hi,

I have a cross poste here:

Retain Value After Date Changes

Hi, I have the sales figures which are updated daily in Sheet 1. This figures in Sheet 1 are updated daily and once we cross over to the next day, it overrides the figures from previous day to the current date. In Sheet 2, I am trying to sum the sales figures automatically from Sheet 1 for...

www.excelforum.com