Using Excel 2010 I have a dropdown in cell B2 (EVENT) linked by Data Validation to a range in Column E (SLIST) of a database (SCHEDULE).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Each “event” has a different number of “active Days” listed in Column I.<o></o>
<o> </o>
I want the procedure to look at whatever is in B2, find the match in Column E, then make the number in Column I of that row the Max Value for the slider.<o></o>
<o> </o>
The Max Value will therefore change automatically when different “events” are selected in B2.<o></o>
<o> </o>
Tried using “INDEX/MATCH” as shown below but with no success<o></o>
<o> </o>
<o> </o>
Each “event” has a different number of “active Days” listed in Column I.<o></o>
<o> </o>
I want the procedure to look at whatever is in B2, find the match in Column E, then make the number in Column I of that row the Max Value for the slider.<o></o>
<o> </o>
The Max Value will therefore change automatically when different “events” are selected in B2.<o></o>
<o> </o>
Tried using “INDEX/MATCH” as shown below but with no success<o></o>
<o> </o>
Code:
[COLOR=black][FONT=Verdana]Sub[/FONT][/COLOR][COLOR=black][FONT=Verdana] SETSLIDEVAL()
ActiveSheet.Shapes.Range(Array("Scroll Bar 4")).Select
With Selection
.Value = 1
.Min = 1
'THIS NEXT LINE IS WHERE IT ALL GOES WRONG - DOESN'T LIKE "EVENT"
.Max = INDEX(SCHEDULE,MATCH(EVENT,SLIST,False),5)
.SmallChange = 1
.LargeChange = 10
.LinkedCell = "C8"
.Display3DShading = True
End With
End Sub [/FONT][/COLOR]