# Thread: Update Cells Based on Cell Selected

1. ## Update Cells Based on Cell Selected

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

2. 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. 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. 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
•