finding rows from a count formula

[b19upj]

Hi,

I have a countif formula that works. It shows me the amount of time past since a certain date, but what i need to to know what row it refers to so i can do something like a vlookup and pull other data from that row that is in different columns. I'll want about 3 different columns including a unique reference.

So for example, when my countif formula finds 5 matches, i would like to know the found matches; Job number, raised date, due date etc.
How can i do this?

TIA

[alansidman]

Suggest you upload a sample worksheet so that we can see what it looks like to better help you solve your issue.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Pete_UK]

It would help if you attached a sample Excel workbook, so we can see how you data is laid out. The FAQ describes how to - basically, click on Go Advanced while composing a post, then scroll down to and click on Manage Attachments, then follow the on-screen prompts.

Pete

[b19upj]

Hi,

Thanks for the quick reply's. The spreadsheet im using is massive and has multiple worksheets so I will try to explain better.

[/INDENT]24h 48h 72h 5 days 10 days >10 days
Completed 5 0 0 0 0 14
Due 3 0 5 0 0 0
Overdue 0 0 0 0 0 10

This is my matrix to see what has been completed/due/overdue in the last/next 24/48/72 hours etc

Formula for this matrix for 24h is:
My formula for Completed is: =COUNTIFS(Data!$AB:$AB,"Completed",Data!$V:$V,">="&NOW()-1)
where AB is the status and V is the completion date
My formula for Due is: =COUNTIFS(Data!$AB:$AB,"In Progress",Data!$T:$T,">="&NOW()+1)
where AB is the status and T is the completion due date
My formula for Overdue is: =COUNTIFS(Data!$AB:$AB,"In Progress",Data!$T:$T,">="&NOW()-1)
where AB is the status and T is the completion due date

This seems to yield me results that im hoping are correct.

So with the above formula populating the above matrix in the form of a count, I would like to reference the count cell/row it is referring to.
So I would like to see what the 5 tasks which were completed in the last 24 hours and the 3 that are due in the next 24 hours.
I'd like to lookup the row being counted and output some columns such as a reference/job ID, Job Description, Notes etc.


How would I go about doing this.

I hope that's a bit clearer. Really sorry if it isn't.