Results 1 to 4 of 4

Thread: Update Cells Based on Cell Selected

  1. #1

    Update Cells Based on Cell Selected



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there


    I am currently working on a workbook used for equipment hire.


    Please find attached an example taken from my workbook.


    The premise for this workbook is that users will be able to select a date on the calendar in the Booking Sheet. When a user selects a date the cells to the right will automatically populate (AB5:AD5, AB8:AD8, AB11:AC11, Etc) depending on what date they have selected. These cells will be populated from Step 2 based upon the date.


    Any help on this will be much appreciated.



    Thanks Mack
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    To get you started, I used right-click rather than a selection change event just so I could move around the sheet without changing anything, so translate the following to that if you want. In Booking Sheet's code-module I put:
    Code:
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Range("u3") = Target.Value
    End Sub
    Clearly it doesn't have to be cell U3.
    Then I put this formula in cell AB5:
    =INDEX('Step 2'!$B$3:$AQ$367,MATCH('Booking Sheet'!$U$3,'Step 2'!$A$3:$A$367,0),MATCH(AB4,'Step 2'!$B$2:$AQ$2,0))
    and copied it across to AD5.

    For this to work, the header above each field has to be the exactly the same as the header in sheet Step 2. (So S. Bag Liner has to be made the same as Sleeping Bag Liner in Step 2 otherwise no match will be found.

    I'll let you do the rest!

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    For the likes of AB18:
    =INDEX('Step 2'!$B$3:$AQ$367,MATCH('Booking Sheet'!$U$3,'Step 2'!$A$3:$A$367,0),MATCH($AB16 & " " & AB17,'Step 2'!$B$2:$AQ$2,0))
    copied across, but again, N2 of sheet Step 2 needs to be:
    Waterproof Jacket Small.

  4. #4
    Thanks a lot p45cal, works perfect! Was trying to wrap my head around this one for sometime with no avail!

    Thanks Mack

Posting Permissions

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