Running a "Last Seen Report" using the data in 1 Tab and viewing it in another Tab

[Wilkesy13]

Hi there,

I fear this may be a hugely technical request but have no doubt in the ability of the amazing people on here.

Question:
I have a spreadsheet that I use for monitoring assets, the assets are kept in one location but out of the pool of assets there are always some that are being used and so can be weeks or months until they are checked. I have the assets listed in a Tab in column A (for discussion purposes let's say 1-50). I have then grouped 5 columns (C-G, H-L, M-Q etc.) under specific dates/days of the week when certain checks are completed and headed as follows "Checked," "Checked By," "Ticket Ref:," "Condition" and "Fault." You can see how it works in the example below and all of the dates that the checks are performed are listed in Row 2.

The "Checked By," "Condition" & "Fault" cells all have drop down lists. When an option is chosen from the "Checked By" column it turns the "Checked" column green and places a 1 in the cell.

What I'd really like to be able to do, in another Tab, is be able to see when an asset was last checked. I have absolutely no idea if this is possible as I have only BASIC excel knowledge but if anyone has an idea or solution I will be very pleased to hear it.

I hope I have explained adequately but in anyone's quest for help, if you have questions please let me know.

For anyone that tackles this mighty challenge I thank you sincerely in advance.

 

[Wilkesy13]

OK, I think I see the issue (seeing your actual data is a big help).
See all the green triangles up in the upper right-corner of your cells with the 1's in them? That usually means that you have numbers entered as text.
If that is the case, then we need to look for the text value of 1, instead of the numeric value of 1.

So change the formula to:

=INDEX(Sheet1!B$2:P$2,MATCH("1",Sheet1!B4:P4))

Ahh, gotcha,

That makes sense, even to me. However, I am now getting the below, having copied your formula.

If it helps here is what is in each cell in the "Checked" columns, I don't know if that would have an impact?

 

[Joe4]

However, I am now getting the below, having copied your formula.

That actually is the correct answer!

It is important to understand how dates work in Excel. Dates are actually stored as numbers, specifically the number of days since 1/0/1900 (enter any date into a cell in Excel and change the format to "General", and you will see the date as Excel does).

So all dates really are in Excel are numbers with special date formats. So just format column B to one of the date format options, and you will see the answer in the format you are more accustomed to.

 

[Wilkesy13]

That actually is the correct answer!

It is important to understand how dates work in Excel. Dates are actually stored as numbers, specifically the number of days since 1/0/1900 (enter any date into a cell in Excel and change the format to "General", and you will see the date as Excel does).

So all dates really are in Excel are numbers with special date formats. So just format column B to one of the date format options, and you will see the answer in the format you are more accustomed to.

Thansk Joe,

I changed the format to a date format but it was returning 31/12/2021 although the figures in the "checked" column only in place for the first 3 dates listed. 31/12/2021 is the last date in the range so I don't know if it's just reading that figure?

As ever I greatly appreciate your help.

 

[Joe4]

This is very hard to debug without having access to your data file.
Is there any way you can upload a copy to a file sharing site and provide a link to it for us?
Just be sure to remove any sensitive data first.

 

[Wilkesy13]

Hi Joe, I wouldn't know where to go for this. I don't use anything like that. Do you have any suggestions?

Sorry for causing hassle on this I can imagine the annoyance I am causing!

Only I could make something that seems simple enough, confusing!

 

[Joe4]

Hi Joe, I wouldn't know where to go for this. I don't use anything like that. Do you have any suggestions?

People often use the free sites like DropBox.com, or if you have a Google Drive or One Drive account.

 

[Wilkesy13]

People often use the free sites like DropBox.com, or if you have a Google Drive or One Drive account.

ok, I'll have a look.

I have had a play and if I change the formula to change the "1" to just 1 the date in te cell the formula is paste in changes between 31/12/2021 and 00/01/1900 respectively. no matter how many 1's there are in the "Checked" column.

Did you say there was somewhere on here I can post the spreadsheet?

 

[Wilkesy13]

People often use the free sites like DropBox.com, or if you have a Google Drive or One Drive account.

Apologies I am fairly simple with this stuff. I have uploaded a copy to Drop Box. What do I need to do now?

 

[Joe4]

Did you say there was somewhere on here I can post the spreadsheet?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Apologies I am fairly simple with this stuff. I have uploaded a copy to Drop Box. What do I need to do now?

Post a link to it here, so we can see it and download it.

 

[Wilkesy13]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


Post a link to it here, so we can see it and download it.

Last Seen Report.xlsx Here's the link, I'm hoping this works!