Using the cell that is selected for the value in a formula

[samc2696]

Hi all,

I am wondering if something is possible at all. I have a calendar on Excel (month-by-month) and then another sheet with all the dates of all the different events that are happening.

I want to be able to click on a date in the calendar and then below, a list of the events that are occurring on that day appears. I more or less have the lookup side of things sorted but I do not know how to make it so that if someone clicks on the date then the list will appear. Essentially, I want to use the selected cell as the 'lookup_value' in the formula.

Is this possible at all? Do I need to use a macro?

Many thanks
Sam

 

[Eric W]

You can't detect the active cell with formulas, but you can with VBA. You can set up a SelectionChange event that detects when a new cell is selected, which would then create your list. If you want me to set something like that up, let me know what your sheet layout is.