Return event based on date

[papaexcel]

See attached worksheet for example.

I want to be able to enter a date and have every event that is occuring on that date listed. I sure hope this is possible.

I prefer to accomplish it without a macro, but if it has to have a macro, that will be fine.

Thanks a ton!

[Palmetto]

I assume, hopefully not in error, that in your real workbook your raw data resides on a separate sheet (it should) and the extracted results will need to go into another sheet.

In this workbook, a dynamic named range was created for the data table:
Name = Database

Criteria cells were created on sheet2, which will hold the results of the filter.
Sheet2 contains the input cell and the filter is applied only to the Start Date.

The worksheet_change event is used so that when a new date is entered in the target cell new filter results are immediately presented. Adjust ranges and references per your actual workbook.

Sheet names in the code are sheet code names not worksheet names.

Please Login or Register  to view this content.

[papaexcel]

Thanks for the quick response Palmetto.

I'm afraid I can't get the your attached file to work properly. Was I supposed to do something with it after I opened it? Never the less, when I change the date nothing happens. I'm sure I'm just not doing something right, so I apologize for my ignorance. Again, thank you for your help.

[Palmetto]

If you were not prompted to enable macros when you opened the file then your Security Settings need to be changed.
If you did receive the prompt, you must enable macros in order for the code to work.

To change your security settings:
Click the Office Orb and choose Excel Options at the lower right. The options dialog will display.
Progressing thru the dialog . . .

Click Trust Center in the left pane of the dialog, then choose Trust Center Settings
Click Macro Settings, then choose "Disable all macros with notification"
Click OK until the dialog is closed.

Close the file and reopen it and enable the macros when prompted.

[papaexcel]

Looks like I don't have the ability to adjust the settings in the Trust Center.

But I know I have the ability to run macros, so maybe if you walk me through pasting the code into VB, I can get it to work that way.

Thanks again

[Palmetto]

If you were not prompted to enable macros when opening the sample workbook I posted then you don't have permissions to run VBA code. Copying and pasting the code will achieve nothing because you will have to close the workbook, re-open it and respond to the prompt.

However . . here's how:

Assuming the target workbook is open:

Right-click the tab of the sheet in which you want to run the code and choose View Code from the pop up context menu.

Copy and paste the above code into the code window.
Make any necessary adjustments to sheet & range references (see my earlier comments on sheet code names).
Press Alt + Q keys to close the VB Editor.
Save and close the file then re-open it.

Note: If you decide to save in the Excel 2007 version, you will need to save the file as a .xlsm file type.

If you don't see a prompt to enable macros, then you need to consult your IT folks since you apparently can't change them in the Trust Center.

[MarvinP]

If you use AutoFilters and some very easy code this is not a hard problem.

See the attached and the code to see if it does what you need.

[papaexcel]

Thanks Palmetto. It worked well, but it only lists the events if the start date equals the date entered in cell B5. I need it to return all events that are active on the date entered in B5. For instance, if I enter in cell B5 10/15/10, I want the "Function" activity to show up.

Also, I will need to transfer the concept to my actual workbook, and I'm afraid I'm not entirely sure how you built the dynamic named ranges. Could you possibly walk me through that as well?

Again, thanks a ton! This will prove invaluable for me.

[papaexcel]

Thanks Marvin, but based on the structure of my actual worksheet, I don't believe Autofilters is the answers.

I appreciate the tip though!