Formula for transferring most recent data from cells

[Tasky]

Bit of a novice, so would appreciate some help with this.

I am trying to find a way to use a formula to extract data from one simple form to another. However, I'm getting stuck with constructing it.

The first form looks like this:

WEEKLY INSPECTION

DATE	ROOM NUMBER	WORK REQUIRED	WORK COMPLETED
01/01/2015	100	PAINTING	02/01/2015
01/02/2015	100	REPLACE BED

<tbody>
</tbody>

And the columns will continue indefinitely as a continuous document that will be constantly updated.

I have a sheet on other tabs which looks like this:

ROOMS

100	101	102	103	104
REPLACE BED

<tbody>
</tbody>

I want this sheet to show the outstanding work required in the rooms, by taking data from the Weekly Inspection Sheet. Think of it a status report of work ongoing.

I was trying to use a VLOOKUP in A2 of the 'Rooms' tab to reference where "100" is entered in the B column, to copy the relevant cell in the C column. However, I only want to copy the most recent entry, and exclude entries where column D (Work Completed) has an entry.

I've been skirting around array formulas using IF, MATCH/INDEX and MAX, but tie myself in knots trying to construct it properly.

Can anyone help me out, please?

 

[Smitty]

Welcome to the Board!

I'd see if a Pivot Table will do what you want. No formulas and easily updateable. In your case you'd put Room Number in the Pivot Table Columns section.

HTH,

 

[Tasky]

Thanks Smitty. Totally wasn't the answer I was looking for.

Gave it a try and it was more suitable for the task than what I had planned! Good shout, sir.

I can expand on my original 'Weekly Inspection' table to include more details and use the Pivot Table to filter the results to make it fit my every whim!