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)

Please Login or Register  to view this content.

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