Update Cells Based on Cell Selected

Mackattack

New member
Joined
Oct 25, 2013
Messages
2
Reaction score
0
Points
0
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
 

Attachments

  • Examplesheet3.xls
    324.5 KB · Views: 20

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
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!
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
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.
 

Mackattack

New member
Joined
Oct 25, 2013
Messages
2
Reaction score
0
Points
0
Thanks a lot p45cal, works perfect! Was trying to wrap my head around this one for sometime with no avail!

Thanks Mack
 
Top