Formula needed to update list based on a cell ref

**WimpieOosthuizen** · 02-06-2014, 09:54 AM

Good day

Please refer to the attached.
I have a calander where appointments are updated on the "Data" sheet.
I need to populate the info (as listed "Events, etc." ) to the "Full" sheet based on the date selected.
There is already a Macro to create the "selectedCell" which if you click a date will appear in B4.
I tried a VLOOKUP but this will only give me one event based on the date, I need at least 13 events to pull accross based on the formula. I tried "Match" and "Index" but not an expert with these formulas
Please assist

**daffodil11** · 02-06-2014, 11:41 AM

I actually just wrote some code very similar to this last night.

I removed the =[selected cells] Worksheet Change Event to AA4 and simply made it change the actual cell value.

This then calls another piece of code which builds an Advanced Filter on the Filter Tab, then grabs each piece of data and manually fits it into each of the merged cell ranges.

I added some names on your Data tab so you can see the Full tab update as you select 2/9, 2/10, 2/11.

Autofill Calendar.xlsm

**WimpieOosthuizen** · 02-06-2014, 12:53 PM

Hi Daffodill

This is totally another way of doing it from what I had in mind, ....... but let me just say this is impressive!!!
Is there any way that you can explain the code for me. I believe in learning especially things that works as this clearly does!! If I understand the code I could find more way of using it.

ALMOST FORGOT!!! Thank you!! this is a great piece of work and code!!!!

**daffodil11** · 02-06-2014, 01:36 PM

Here's the post I wrote last night, which was very similar. It even has an explanation of the code.

https://www.excelforum.com/showthread.php?t=987158

Your code was modified slightly to target horizontal merged cells instead.

I used 3 variables as starting positions for the paste-to range: n = 5, p = 21, w = 35 since these are the rows we begin pasting to.

Each column's data is then pasted to AA5:BO5, AA21:BO21, AA35:BO35. At the end of the first round of pasting, it adds 1 to each variable, and then cycles through again this time pasting to AA6:BO6, AA22:BO22, AA36:BO36 and so on. Other than that, all I did was change the paste from Columns and Paste to Columns and nearly everything is indentical.

You can look at your code by hitting Alt+F11 and going to Module 1.

The worksheet_change event is occuring on your Full worksheet, right below the original.

I'm quite the novice at code myself, so it took about 3 hours to write what I did for last night's post. I've used Advanced Filtering in the past, so the first chunk of code came straight out of my head, as did turning off the ScreenUpdating and Clearing cell contents. The other 3 blocks came from an article I found online on how to paste to a merged cell. I just tinkered with the code, spliced in what I thought I needed, and then debugged it until it worked the way I needed.

There's a good chance an expert could do the same work in less lines (and certainly in less time), in much the same way a novice Excel user might use =A1+B1+C1+D1+E1 and a more experienced person might write =SUM(A1:E1). But mine works all the same, and it's good starting point.