SOLVED

# Get latest status on an overview sheet

Copper Contributor

# Get latest status on an overview sheet

Hey guys!

I hope someone can help me out. I'm creating a table where I want excel to retrieve the latest information from another table. I have an overview worksheet where I want excel to show the latest updates out of the entry worksheet.

This is the entry worksheet:

This is the overview worksheet:

So I'm now trying to create a formula for the C, D and E row. They have to retrieve the latest entry about a certain room of the table on the Entry tab. So it has to filter for room and for the latest entry date and then copy the information. I'm pretty stuck here. Can someone help out how I can reach that sort of automation?

I'm looking forward to any help 🙂

3 Replies
best response confirmed by SannekeVisser (Copper Contributor)
Solution

# Re: Get latest status on an overview sheet

See the attached sample workbook.

# Re: Get latest status on an overview sheet

@HansVogelaar Thanks a lot!

A next question:

I want a number in the selected cell. The number is a representation of the Status.

The correspending number can be found in another table:

Then with the number I want to be able to calculate what percentage is busy, and in the overview tab I want a row with pictograms (red(#2), yellow(#1), green(#0).

When I try to do it now, it doesn't work. I use this formula:

=SEARCH([@Status];Table3[Status];Table3[Code])

But it gives me the wrong code back, so I'm not sure where it goes wrong.

This is what I have now:

https://1drv.ms/x/s!AoVAAPcVWxf-heN4IYJxjTMSg4m5ew?e=bhfrYG

# Re: Get latest status on an overview sheet

Do you have Microsoft 365 or Office 2021? If so, you can use

=XLOOKUP([@Status];StatCod[Status];StatCod[Code])

Otherwise:

=VLOOKUP([@Status];StatCod;2;FALSE)

See the attached version.

1 best response

Accepted Solutions
best response confirmed by SannekeVisser (Copper Contributor)
Solution

# Re: Get latest status on an overview sheet

See the attached sample workbook.