Referencing a name and its entailing information for another worksheet

[Somethingoranother]

Dear Excelforum,

First of all, I am quite new to excel, and forums in general, so I apologize in advance for my, perhaps, simple questions and imprecise jargon.

Here's some relevant background information on the issue I'm working on. I'm developing a registry for a daycare (all names, DOB, etc. in the below posted Excel sheet are generic). Importantly, the registry says the name (Column A), the birth date (Column B), the category to which the child belongs - "K" for children older than 18mths and "B" for children younger than 18mth- (Column C), the number of places each child occupies - "K"=1 and "B" = 1.5 - (Column D), and the date when each child has, or will, turn 18 mths old (Column E) - i.e. when the child's respective "category", and the corresponding "count", has changed, or will change, from "B" to "K", and 1 to 1.5. I think I got the basic functions put in here correctly.

Also, this registry needs to correspond to a monthly worksheet that says which child will come on which days; Namely, worksheets "January" and "February". Here's the important part, and let me illustrate it by highlighting one example: see "Amanda" in the attached excel sheet. As of right now she is in category "B", and thus she counts as 1.5 spaces in the daycare. But, as cell E11 indicates on the registry worksheet, she will be turning 18mths old on February 14, 2016. So What I need is for the sheet in February to automatically change from 1.5 to 1 in February, but not in January.

Also, it would be great if I could just type in any same, for example, "Amanda" and the correct number could be filled in for the days that she will be in the daycare.

If there are any tips or questions, then please just let me know.

Best,


Registry 151109.xlsx

[popipipo]

Something like this??

[Somethingoranother]

Hi Popipipo,

Thanks a lot for your help. The functions that you added in worked really well.

[Somethingoranother]

Also can you explain to me why there is a +1 at the end of this : =EOMONTH(Registry!B3,18)+1

[popipipo]

Eomonth gives last day of the month.
+1 is the next day thus the first day of the next month.