Formula or VBA for a Scroll bar to match min and max values of a variable cell?

[gaftalik]

Hi everyone,

In a table i have :
E1= Apple; F1= 0.2%; G1= 1.5%
E2= Banana; F2= 1.5%; G2= 3%
E3= Pear; F3= 1%; G3= 4%
E4= Cherry; F4= 4% ; G4= 10%
E5= Peach; F5= 5% ; G5= 20%

Column F represents the minimum % and column G represents maximum %

In A1 i have a data validation drop down list E1:E5 (Apple,Banana;Pear,Cherry,Peach)

Whatever selection of A1 value, i need a scroll bar form linked to cell "C1" to automatically set the minimum & the maximum values matched in F1:G5

Ex: If A1= "Pear" the scroll bar should scroll down C1 to a min of 1% and scroll up a max of 4%
If A1= "Peach" it should scroll down C1 to a min of 5% and scroll up a max of 20%

Could that be done with a formula? cause i dont know VBA, if so would you kindly find the easiest VBA code with instructions please?

I appreciate very much your support !

 

[RasGhul]

Hi,

Does this suit?

Book1
	A	B	C	D	E	F	G	H
1	Peach		20.0%		Fruits	Min	Max	DV
2					Apple	0.2%	2%	0.05
3					Banana	1.5%	3%	0.2
4					Pear	1%	4%
5					Cheery	4%	10%
6					Peach	5%	20%
Sheet1

Cell Formulas
Range	Formula
H2	=VLOOKUP($A$1,$E$2:$G$6,ROWS($H$1:H2),0)
H3	=VLOOKUP($A$1,$E$2:$G$6,ROWS($H$1:H3),0)

 

[gaftalik]

Thank you for your reply.
Your formulas lookup the min and max and returns it in cells, what i need exactly is that the scroll bar linked to C1 to be set to a max of H3 and minimum of H2 meaning that the scroll bar should not scroll down to less than 5% and not to scroll up more than 20 % as in your Peach example.

Sorry the scroll bar was not generated by the html maker, it's between C7 and B7

Book1
	A	B	C	D	E	F	G	H	I
1	Peach		5%		Fruits	Min	Max	DV
2					Apple	0.20%	1.50%	0.05
3					Banana	1.50%	3.00%	0.2
4					Pear	1.00%	4.00%
5					Cherry	4.00%	10.00%
6					Peach	5.00%	20.00%
7	Min			Max
8	5%			20%
9		Cell link = $C$1
10
11
Sheet1

Cell Formulas
Range	Formula
H2	=VLOOKUP($A$1,$E$2:$G$6,ROWS($H$1:H2),0)
H3	=VLOOKUP($A$1,$E$2:$G$6,ROWS($H$1:H3),0)

 

[gaftalik]

Hi everyone,

I found the following vba code which solved the problem :

Private Sub ScrollBar1_Change()
ActiveSheet.ScrollBar1.Max = Range("$H2").Value
ActiveSheet.ScrollBar1.Min = Range("H3").Value
End Sub

Thank you!