Designing a spreadsheet to show the most up to date entries - 4 Different Offices

[Smokeyham]

Hello,

I am keeping track of our emergency supplies across four different offices. The supplies kept at the offices are all the same, namely:

Gloves - Small
Gloves - Medium
Gloves - Large
Gloves - XLarge
N95 Masks

Four different people will be sending me reports on different days (they have differing days off, etc.) and I would like to set up a system where I can quickly report on our total level of supplies of each item, using the latest report from each office. Ideally the report would show the number of each item in each office, and also the last date when a report was received and then a total for each item.

Any suggestions on how I set up such a system? I'm thinking something along the lines of a pivot table, but my challenge is having it only pick up the latest entry for each office.


Below is a sample of the type of data I am working with. I do want to keep a historic record of usage at each office, so just updating with the latest entry is not a good option.

Date	Office	Gloves -Small	Gloves-Medium	Gloves- Large	Gloves- X-Large	XX-Large	Total Gloves	N 95 Masks
4/13/2020	CAO	300	1,350	1,250	1,050	150	4,100	200
5/1/2020	SFO	150	1,350	1,050	950	150	3,650	198
5/27/2020	FF0	100	1,250	1,150	700	150	3,350	310
6/2/2020	SAO	100	1,325	950	700	150	3,225	290
6/2/2020	CAO	250	1,000	1,500	900	200	3,850	180

 

[Smokeyham]

Checking back in..... I engineered a solution to this.

I created a pivot table in columns A&B. This returns the latest date that was done for each office. This article (https://www.excel-university.com/most-recent-transaction-date/) helped to figure out how to get the latest date in a Pivot Table. I used a pivot table because I have relatively few offices and the data is refreshed fairly often.

In the columns to the right (Column C ->>) I have formulas that say to look for the item listed above (for instance "Gloves - Small") and then to look for that value for the date and office shown in columns A & B. The formulas to do this are shown below.

Hope this helps.

Inventory Tracking - Glove & Masks.xlsx
	A	B	C	D	E	F	G	H	I
2			Gloves -Small	Gloves-Medium	Gloves- Large	Gloves- X-Large	XX-Large	N 95 Masks	Total Gloves
3	Office	Date of Latest Count
4	AO	8/5/2020	1,500	1,500	50	50	0	600	3,100
5	CAO	8/10/2020	0	75	225	75	100	16	475
6	FFO	8/2/2020	400	300	550	900	900	483	3,050
7	SAO	8/12/2020	200	200	500	150	150	33	1,200
8	SFO	8/7/2020	100	1,200	1,350	1,650	1,650	115	5,950
9
10		Total Inventory	2,200	3,275	2,675	2,825	2,800	1,247	13,775
Most Current Inventory

Cell Formulas
Range	Formula
C4:C8	C4	=IF(LEN(A4)>0,INDEX(RecordByOffice[#All],MATCH(1,(RecordByOffice[[#All],[Date]]=$B4)*(RecordByOffice[[#All],[Office]]=$A4),0),3),"")
D4:D8	D4	=IF(LEN(A4)>0,INDEX(RecordByOffice[#All],MATCH(1,(RecordByOffice[[#All],[Date]]=$B4)*(RecordByOffice[[#All],[Office]]=$A4),0),4),"")
E4:E8	E4	=IF(LEN(A4)>0,INDEX(RecordByOffice[#All],MATCH(1,(RecordByOffice[[#All],[Date]]=$B4)*(RecordByOffice[[#All],[Office]]=$A4),0),5),"")
F4:F8	F4	=IF(LEN(A4)>0,INDEX(RecordByOffice[#All],MATCH(1,(RecordByOffice[[#All],[Date]]=$B4)*(RecordByOffice[[#All],[Office]]=$A4),0),6),"")
G4:G5	G4	=IF(LEN(A4)>0,INDEX(RecordByOffice[#All],MATCH(1,(RecordByOffice[[#All],[Date]]=$B4)*(RecordByOffice[[#All],[Office]]=$A4),0),7),"")
H4:H8	H4	=IF(LEN(A4)>0,INDEX(RecordByOffice[#All],MATCH(1,(RecordByOffice[[#All],[Date]]=$B4)*(RecordByOffice[[#All],[Office]]=$A4),0),8),"")
I4:I8	I4	=IF(LEN(A4)>0,SUM(C4:G4),"")
G6:G8	G6	=IF(LEN(A6)>0,INDEX(RecordByOffice[#All],MATCH(1,(RecordByOffice[[#All],[Date]]=$B6)*(RecordByOffice[[#All],[Office]]=$A6),0),6),"")
C10:I10	C10	=SUM(C4:C8)