Place Items with a specific Date in Monthly Calendar

**simmo86** · 12-23-2009, 08:33 AM

Hi,

I have a whole list of information i.e. First Name (column A), Last Name (column B), anniversary (column C). Then on another sheet I have a list of all the months of the year in row 1.

I am trying to create a lookup that will place the persons surname under the corresponding month.

Thanks in advance.

Alex

**NBVC** · 12-23-2009, 08:50 AM

It would be easier if you could post a workbook showing your setup

**Ron Coderre** · 12-23-2009, 08:59 AM

See the attached workbook

With
On Sheet1...
Col_A contains First Names (beginning in Row_2)
Col_B contains Last Names (beginning in Row_2)
Col_C contains Anniversary Dates (beginning in Row_2)

and
On Sheet2...
A1:L1 contains Jan, Feb,...Dec

This will list names with anniv dates in the same month s Row_1.
(ARRAY FORMULA...committed with CTRL+SHIFT+ENTER, instead of just ENTER)

Code:

A2: =IF(SUMPRODUCT(--(TEXT(Sheet1!$C$2:$C$20,"mmm")=A$1))>=ROWS($2:2),
INDEX(Sheet1!$B$1:$B$20&", "&Sheet1!$A$1:$A$20,SMALL(IF(TEXT(
Sheet1!$C$2:$C$20,"mmm")=A$1,ROW(Sheet1!$C$2:$C$20)),ROWS($2:2))),"")

Copy A2 into A3 and down as far as you need.
Copy that series of formulas and paste them under each month name.

Is that something you can work with?

**martindwilson** · 12-23-2009, 09:06 AM

non array with helper colums attached

**simmo86** · 12-23-2009, 11:08 AM

Thank you all, that last one worked perfectly! And I am sure I will be able to use that first one in the future.

Thanks again,

Alex

P.S. Happy Christmas

**simmo86** · 01-11-2010, 06:06 AM

One other thing on the same subject, is it possible to have the surname as a list on the left hand side and then their first name returning a value in the calendar. From the attached document, I mean instead of returning sheet 2, returning sheet 3 instead? I have just moved cells around on sheet 3 for the purpose of this illustration.

Thanks in advance,

Alex

**martindwilson** · 01-12-2010, 06:18 AM

im not sure exactly , i confused myself ! is it only one person or several? put another example with some different names so i can uncloud my mind!

**simmo86** · 01-12-2010, 07:43 AM

Hi Sorry about that, I have removed what you did previously and have shown how I am trying to make the two sheets work together. These sheets dont have formulas in them but that is what I am trying to create to automate.

Any suggestions?

Thanks for all your time on this!

Kind regards,

Alex

**NBVC** · 01-12-2010, 11:20 AM

In Sheet2!B2:

=SUMPRODUCT(--(Sheet1!$B$1:$B$4=$A2),--(TEXT(Sheet1!$D$1:$D$4,"mmm-yy")=TEXT(B$1,"mmm-yy")),Sheet1!$C$1:$C$4)

adjust ranges to suit.

copied across the table...

To hide the Zeroes.. go to Tool|Options and in View tab, uncheck Zero Values