Hey guys! Sorry in advance for the vague title, couldn't figure out how to summarize this problem succinctly. Here's my issue:
In my spreadsheet, I have two sheets: Calendar, and List. The Calendar sheet is essentially the "face" sheet. It's formatted to look nice and be easy to read (I've attached an example). The List sheet is just that. A list of dates in column A, school names (as three letter acronyms) in column B, and student names in column C. The cells in the Calendar sheet contain a list of student names followed by the school acronym in parentheses and then a soft return. The names and school acronyms are retrieved from the rows with corresponding dates in the List sheet.
To do this, I followed the advice given in this thread for returning the results of multiple values to a single cell with a UDF, and modified the code to fit my specific situation. I wound up with this:
So the formula in each cell follows this format (I'll use the entry from B17 in the example): =getnames(B16,List!$A$2:$A$85)
The DRng in the formula references the cell that contains the date it corresponds to (which is always the cell directly above it), so any future iterations of this spreadsheet only need to have those dates updated and the formulas will still return the correct results. This works great, except that any time I enter new information on the List sheet, the last value in the corresponding cell of the Calendar sheet is returned blank. So, for example, if I enter two student names from the GPT school with a date of 11/3/2012 (let's say John Smith and Jim Smith), the result in the 11/3/2012 cell will be :
John Smith (GPT)
I figured out that if I enter ANYTHING in column A of the next row (after the one with Jim Smith), then immediately delete it, the result then returns properly. Unfortunately, modifying this UDF is my first introduction to VBA, so I lack the knowledge to understand why this happens. Can anybody help?