Trying to design a Creche Enrolment schedule

[Jfin]

OK ive posted a few queries here today as im trying to make a few things work at different stages.

1. Im TRYING to create a not so simple programme with Excel for My Wifes Creche - She would like to have a programme that, when she gets a phone call from a parent to check availabilty in lets say - 3 months time, it reports back with a number of availabiltys for that date in a specific room.

2. Rooms are broken in 4 categories ( up-to 12 mths old "Baby room" 12 mths to 2 yrs "Wobblers room" 2-4 yrs "Toddlers room and finaly Playschool,)

3.So far what i have done is this =IF(G2<$A$1,"",IF(G2<$A$1039,"Baby",IF(G2<$A$1404,"Wobbler", IF(G2<$A$1770,"Toddler",IF(G2<$A$2290,"Playschool",
which gives me a room from inputting the Date of Birth.

4. ive then used the form button to give me the option to add edit and delete entries from the headings (First name Last name Date of Birth Room -but this will show once DOB is entered.

5. i have tried to run the =COUNTIF(A2:A7,"Baby") to try and get the total baby spaces taken up at a given date however this wont work for me and isnt giving any error message, just a blank cell.

Can anybody out there help me out, im finding it hard to even work in my own head how this could work never mind searching the help database.

[VBA Noob]

Jfin,

Please read the forum rules below and then amend your title

Many thanks

VBA Noob

[MickG]

Hi Jfin,
My initial thoughts are that if you had a list of date for every day of the year in column "A".
These Dates where then sub Divided into say 4 groups of 5 each Sub division being of the same date.
At the start of each sub group you would have the word "Baby" , at the start of the next Five lines the word "Wobbler " and so on.
This would give you dates & Rows across, that you could then fill in when booking, Based on the Headings in row (1) "Child's Name" , "Client's Name" "Contact Number" etc.
If you wanted to see or enter a Booking, You could run a bit of code to find Particular Dates, Names etc

Attached is a basic bit of code, If you run it in a new sheet it will Enter in the Basic Dates and Headings for the year.
Information Boxes, CommandButton etc. could be added, to enhance is functionality
If you would like to follow this up, and can think of some ideas to improve it Call back

Dim Dt, Cd, oDate
Range("a1") = "Date"
Range("b1") = "Child's Name"
Range("c1") = "Client's Name"
Range("D1") = "Contact Number"
oDate = "1/1/08"
For Dt = 2 To 7320
If Dt > 2 Then oDate = DateAdd("d", 1, oDate)

For Cd = 1 To 20

Select Case Cd
Case Is = 1
Rows(Dt).Interior.ColorIndex = 27
Cells(Dt, 1) = "Baby"
Case Is = 6
Cells(Dt, 1) = "Wobbler"
Rows(Dt).Interior.ColorIndex = 34
Case Is = 11
Cells(Dt, 1) = "Toddler"
Rows(Dt).Interior.ColorIndex = 35
Case Is = 16
Cells(Dt, 1) = "Play School"
Rows(Dt).Interior.ColorIndex = 38
Case Else
Cells(Dt, 1) = oDate
End Select

Dt = Dt + 1

Next Cd

Dt = Dt - 1
Next Dt

Regards Mick

[Jfin]

OK ive posted a few queries here today as im trying to make a few things work at different stages.

1. Im TRYING to create a not so simple programme with Excel for My Wifes Creche - She would like to have a programme that, when she gets a phone call from a parent to check availabilty in lets say - 3 months time, it reports back with a number of availabiltys for that date in a specific room.

2. Rooms are broken in 4 categories ( up-to 12 mths old "Baby room" 12 mths to 2 yrs "Wobblers room" 2-4 yrs "Toddlers room and finaly Playschool,)

3.So far what i have done is this =IF(G2<$A$1,"",IF(G2<$A$1039,"Baby",IF(G2<$A$1404,"Wobbler", IF(G2<$A$1770,"Toddler",IF(G2<$A$2290,"Playschool",
which gives me a room from inputting the Date of Birth.

4. ive then used the form button to give me the option to add edit and delete entries from the headings (First name Last name Date of Birth Room -but this will show once DOB is entered.

5. i have tried to run the =COUNTIF(A2:A7,"Baby") to try and get the total baby spaces taken up at a given date however this wont work for me and isnt giving any error message, just a blank cell.

Can anybody out there help me out, im finding it hard to even work in my own head how this could work never mind searching the help database.

[Simon Lloyd]

Jfin, this is a lot of work, have you got anything yourself?, if so why not share it then we can enhance what you have and give advice on what is needed or possible!

[Jfin]

Ive been on holidays for the last couple of weeks so sorry for not getting back, Thanks for the time and effort Mick,

I dont have any coding as such done all i have been doing is the following.

=IF(G2<$A$1,"",IF(G2<$A$1039,"Baby",IF(G2<$A$1404,"Wobbler", IF(G2<$A$1770,"Toddler",IF(G2<$A$2290,"Playschool","Please use DD/MM/YYYY")))))

On the left i have Dates and in the next cell i have Headings listed beside the dates i.e. Baby, Wobbler etc.

On the capture form i have headings First name Last name DOB, Room.

When i input DOB the room is automaticaly found i.e. if the date is found "less than A1039" then the room will be Baby. (Im sorry if im stating the very obvious here)

So far im embarrassed to say i havent gotten anywhere else on this, It took me about 10 hours to learn what i have from excel Help, I did have some VB training well 2 years in college 7 years ago - most of it now forgotten but i know the basics.

Mick i did run your code in a new excel sheet and brought up VB and pasted it in there (is this correct?) and it gives me an error (compile error "invalid outside procedure) and its highlighting "DATE"

[Simon Lloyd]

If you are using a sample workbook to test things out why not upload it so that people helping can see your structure and help and advise you?

[Jfin]

Dont know if i have attached correctly let me know, Thanks

EDIT Simon Lloyd: File has 2200+ rows of info so has been zipped in xl97-2003 format, it used to be xl2007 so some minor formatting has been lost (shading)