+ Reply to Thread
Results 1 to 4 of 4

Excel formula development for ranges and dates in a calendar.

  1. #1
    Registered User
    Join Date
    05-19-2008
    Posts
    8

    Excel formula development for ranges and dates in a calendar.

    Hi,

    I currently have a formula in excel which fills in a calendar from a booking list. However, I need to slightly develop the formula so that the calendar is filled automatically across several dates, rather than just one date, from an additional entry.

    The formula currently in each cell within the calendar is:

    =IF(SUMPRODUCT((INDIRECT("$B$14:$B$5000")=$A4)*(INDIRECT("$F$14:$F$5000")=SUBSTITUTE(B$3,"Room","")+0)),"X","")

    It basically puts an "X" in the cell within the calendar in the right date and place if it matches the date and room number in the booking list. However, I would also like it to fill in across dates for the amount of nights entered separately in the booking list.

    Please could someone first tell me if the formula I used is the most effective and also how this can be done.

    I have attached the example excel file. It should work, but if not it is also shown below.

    Kind regards,

    Daniel

    PHP Code: 
    Calendar                        
        Room                    
    Date    1    2    3    4    5    
    1
    -May-08                        
    2
    -May-08        X                
    3
    -May-08                        
    4
    -May-08                        
    5
    -May-08                        
    6
    -May-08                        
                            
    Booking 
    List                        
    Occupant    Date of Booking     Number of Nights    Number of Persons    Room Type    Room Number    
                            
    Mr Z    02
    /05/2008    2    1    Single    2 
    Attached Files Attached Files

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    Try this formula in B4:
    Please Login or Register  to view this content.

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Dan99,

    The attached workbook contains the macro show here. This macro will add the "X" to the appropriate dates. If the number of nights exceeds the days listed, the number of nights is adjusted to what is available. This happens as you type data into any row greater than 12. If you want to expand your table to include more days or rooms, let me know.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-19-2008
    Posts
    8
    Thanks guys, i'll use the info and get back to you if I need to.

    Regards,

    Daniel

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1