Hello, I am trying to create a booking calendar for boarding kennels.
I have a separate sheet for each client, containing their personal info and dates of their bookings. I have downloaded a basic calendar but would like it to show certain information.
Is it possible to create a formula which will highlight each cell on the calendar, for each kennel when it is occupied, and show the name?
Many thanks for any suggestions
CALENDAR.JPGCUSTOMER.JPG
Last edited by ajs100; 05-10-2014 at 12:50 PM.
you may be better off putting a sample spreadsheet , rather than an image
you could do with conditional formatting and lookups - BUT i would need to change the layout of your data entry - so see what other replies you get
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
DATABASE example.xls
hello, thank you for your reply, I am trying to attach a copy now
P.S. I wouldn't mind changing the layout if you think it is useful
I am still trying to solve this problem, maybe I should explain it a bit more
Each cell on the calendar represents a place in each kennel for each date. I would really like to find some sort of lookup function to put in the calendar cells:
-IF todays date is on or between the booking dates in columns D and G for each client's worksheet
-AND the pen/kennel number at the left of that row in the calendar matches the pen number on the client's worksheet, columns AC to AG
-SHOW the name of the pet(s) in the calendar cell for that particular date and kennel.
By the way I am doing this for an elderly friend with an equally elderly computer and version of excel
Thank you for any suggestions
what version of excel ?
I think it's 2003.
OK, so i can have look over the next few days, maybe with a layout change, but hopefully, a more knowledgeable member can work with your data structure
and a little bit of VBA
thank you etaf, much appreciated. Am still trying various ways myself, and by the way it might be easier to refer to the data sheet which shows everything on one page. If I get there before anyone else, will obviously post my findings. Cheers
yes, i was looking at "all data"
if it was structured as a database then it maybe easier to work with
so
owner name, PET, PEN, Startdate, stopdate
you can then do a lookup using
vlookup or index/match - (vlookup with true - so it takes the nearest lower value )
along with PEN ID - so there can only be 1 record for that entry
then conditional formatting - again looking at the start and end dates - but may not work.....
Thanks again, it sounds like what I am trying to do. I am not quite sure what to use where... what would I match? The kennel number? How could I use this for dates between or equal to booking dates?
Sorry am not too proficient with this... it is all a bit new
Not quite working out the way I had hoped , sorry about that
No worries, I really appreciate you having a look and suggesting ideas... it could take forever but am still trying and determined to find a way
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks