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.

 

[Joe4]

Let's say that your data is on "Sheet1", and the sheet you want to return this value to is "Sheet2".
And let's say that the Asset value you want to look up is in cell A2.
And I am assuming that the value of "1" will only appear in the "Checked" columns, and not in any of the other columns.

Then place this formula in cell B2 to get the last date for that Asset that has a "1" in the "Checked" column:

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

You would obviously change the column reference "P" to match whatever your last column on Sheet1 is.

 

[Wilkesy13]

Let's say that your data is on "Sheet1", and the sheet you want to return this value to is "Sheet2".
And let's say that the Asset value you want to look up is in cell A2.
And I am assuming that the value of "1" will only appear in the "Checked" columns, and not in any of the other columns.

Then place this formula in cell B2 to get the last date for that Asset that has a "1" in the "Checked" column:

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

You would obviously change the column reference "P" to match whatever your last column on Sheet1 is.

Joe thanks so much for looking at this and your very quick response.

I think i'm doing something wrong though as I can't get it to work.

You are right, - the data is on Sheet 1 and I want to see the requested data in Sheet 2.
- The assets are listed from 1-228 from Cell A4-A232 so just the A column.
- The value of 1 will only appear in the "Checked" column once a name has been selected from the "Checked By" column.

Do I place your formula in B2 of Sheet 2? and is there a way of indicating in Sheet 2 the last time an asset was "Checked"

So sorry to cause you any sort of headache with this.

 

[Joe4]

I was working off of the structure you posted for Sheet1 in your original post.
Can you post your structure for Sheet2?

 

[Wilkesy13]

I was working off of the structure you posted for Sheet1 in your original post.
Can you post your structure for Sheet2?

Ahh my apologies. I don't currently have any structure for Sheet2 as I wasn't sure on the best way of doing it. So thought I'd go on the advice gathered here.

 

[Joe4]

OK, I should have clarified one thing.
So, your assets are listed on Sheet1, like this:

- The assets are listed from 1-228 from Cell A4-A232 so just the A column.

My code would work if they are listed in the exact same order on Sheet2.
So, you would have the Asset values in column A, and then you would place the formula in column B.
You would place the formula I posted for the very first asset listed, and then copy it down for the others.

To ensure that the assets are listed in the same order, you could use a formula to populate column A on Sheet2, i.e.

=Sheet1!A4

If that doesn't work for you, let us know, and we will see what else we can come up with.

 

[Wilkesy13]

OK, I should have clarified one thing.
So, your assets are listed on Sheet1, like this:


My code would work if they are listed in the exact same order on Sheet2.
So, you would have the Asset values in column A, and then you would place the formula in column B.
You would place the formula I posted for the very first asset listed, and then copy it down for the others.

To ensure that the assets are listed in the same order, you could use a formula to populate column A on Sheet2, i.e.

=Sheet1!A4

If that doesn't work for you, let us know, and we will see what else we can come up with.

Hi Joe,

I tried copying the assets into column A exactly as in Sheet 1 but still cannot get it to work. I am excel blind so I am most likely doing something simple, wrong.

Any thoughts as ever would be appreciated

 

[Joe4]

What is happening?
Are you getting errors or unexpected results?

Please post two images:
1. One of your actual data (so I can see how it really looks when data is in there).
2. One of your attempt to set up the second sheet.

Note that 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.

 

[Wilkesy13]

What is happening?
Are you getting errors or unexpected results?

Please post two images:
1. One of your actual data (so I can see how it really looks when data is in there).
2. One of your attempt to set up the second sheet.

Note that 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.

Thanks Joe,

Please find the below images with how the actual data is presented as well as my attempt at using your formula.

 

[Joe4]

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))