Reference certain cells in a table based on cell criteria

[Mr_Bill]

I have a table that shows the days left until an item is due. I would like to know can these be made to echo 3 of the due items with the least time left onto another sheet?

Prior to asking for help
I looked into a formula based idea but vlookup needs a value to compare to and not what I was wanting. I also thought maybe using a script to auto sort the table lowest to largest and than just use something like =A3 =B3 =D3 =E3 in the sheet I want to show the data. However I would like this to be script driven as I am prone to deleting formulas in the cells and currently the project this will end up in has no formulas on the dashboard to prevent me from messing it up

Here is the sample workbook with a Upcoming sheet that shows the outcome I am trying for with type of data that should be echoed.

Also if this is a complex thing to do please let me know and I will shove it on the back burner for later in the future.

Here is the sample workbook

Thank you for looking

[Mr_Bill]

okay I have gotten closer but 3 issues.
1) How do I make this fire when the workbook opens but only display 3 results in Test sheet so it would overwrite itself
2) only paste value no formatting
3) if less than 3 assignments post what is there if none are there display "No Assignments Due"

So far this works but doesnt sort and doesnt start with workbook

Please Login or Register  to view this content.

Revised the autofilter isnt working was in my head.

[Leith Ross]

Hello Mr_Bill,

The attached workbook has been updated with the macro code below. This will load assignments that are 3 days or less into the table when the workbook opens.

There is a Text Box that is hidden with the message "No Assignments Due". This is displayed only when there are no assignments 3 days or less pending.

Module1 Macro Code

Please Login or Register  to view this content.

ThisWorkbook Module Code

Please Login or Register  to view this content.

[Mr_Bill]

whoa that is way more work than I thought it would be. Thank you very much Would it be too much to ask for it to limit the results to only the lowest 3?

[Leith Ross]

Hello Mr_Bill,

I can make that happen.

[Mr_Bill]

That will be awesome thank you. Also not sure if you noticed with this version but when you load it you get the initial ones will fill in and if you save exit and come back it adds more to the list instead of over writing the ones already there.