Issue using array to find values across multiple worksheets

I am trying to create a sheet that will allow me to find items within a date range across multiple worksheets. I have been working with a formula that I found online, so if there is a better formula, please tell me.

First, I'm having trouble entering a date range; I can only lookup after a certain date (d1).

Secondly, I can look at values on one sheet by entering the sheet name, but once I try to add the named list "Events" (which is a listing of the names of the worksheets) using the same formula I can't get any values.

Ideally, I would like to get a list of items that must be completed within a date range and have them all show up on one page instead of looking through 50+ worksheets.

In the attached example, I have 3 events. On the "Sheet Test" page, that is where I was able to look across one sheet to find items that need to be completed. The Sheet "Lookup Test" has the named list "Events" where I would like the names of the worksheets that will be involved in the search.

Any help will be appreciated.

mehmetcik,

Thank you for your work on this for me. It looks really good and really, I am very thankful. I have been working on this for a couple of days now and no one around me has been able to help. I'm trying to read through the scripting, but I am new to VBA and I am having some trouble. I do have a couple of questions about the way it operates and a few questions about how it could sort.

• One the event pages, there were formulas that determined what the dates would be. Once the "Update" sheet was pressed, it seems to remove those formulas. Is there a way to keep those formulas intact? It would populate the next date with the previous paperwork was completed.
  
• Also, on the "Lookup Test" sheet, is there a way to set the formatting of the column width so you don't have to expand the columns every time? Can the events be listed by their date relevant to today's date? For example, if Event1 is after Event2, can Event2 be listed first in the list?
  
• I tried adding a worksheet and it wan't added to the list of events in "Lookup Test"

mehmetcik,

Thank you for your work on this. I am trying to condense a few things since it appears that I don;t need a few columns and I was trying to drop the start of the information on "Lookup Test" a few rows to "A5" instead of "A3". It is necessary to have

PHP Code: 

Range("A3").FormulaR1C1 = "'name=Events" 


? I was just trying to show you that I had a named list called "Events" I am attaching how I would like the ending form to look with some of the edits that I think are still returning the correct results.

It appears in that if I change the start date from 7/23/2018 to 7/1/2018, it appears that there are no results. Any idea why that is? Am i doing something wrong?

To answer the question about negative numbers, that task will take place after the event. The formulas at the top are setting dates before the event and the negative numbers are setting dates after the event.

Thank you so much for your help with this. It is really helping me out!

mehmetcik,

I am still not getting all of the items when I search from 7/1/2018 to 8/16/2018. I am not sure what is happening.