Search This Blog

Thursday, 26 October 2017

ENABLE OR DISABLE CHECKBOX ON CLICK OF ANOTHER CHECKBOX



1) Write the below code on the initialize event of  your userform

Private Sub UserForm_Initialize()
Me.ChildBox1.Enabled = False
Me.ChildBox2.Enabled = False
End Sub

It makes the Child-1 & Child-2 disabled which means only the Parent checkbox will be enabled when the userform initializes.

2) Write the below code on Click event of the Parent checkbox.

Private Sub parentbox_Click()
If Me.parentbox.Value = True Then
Me.ChildBox1.Enabled = True
Me.ChildBox2.Enabled = True
ElseIf Me.parentbox.Value = False Then
Me.ChildBox1.Enabled = False
Me.ChildBox2.Enabled = False
End If
End Sub

It makes the Child-1 and Child-2 enabled when Parent is checked and disabled when Parent is unchecked.

Is the post useful? 

Please put your comments in the comment section and subscribe the blog:) Thank you.



Friday, 15 September 2017

FETCHING DATA FROM A TABLE BASED ON MORE THAN ONE LOOKUP VALUE


Here I want to retrieve values for  Data1, Data2, Data3 & Data4 from the given dataset (A1:F10) based on two lookup values that is EmpCode and Dept.

Formula used in range C13:F13

=VLOOKUP(A13&B13,CHOOSE({1,2,3,4,5},A1:A10&B1:B10,C1:C10,D1:D10,E1:E10,F1:F10),{2,3,4,5},0) with CSE

This can also be done using the Index/Match function. Below is the formula:

=INDEX($A$1:$F$10,MATCH(1,IF($A$1:$A$10=$A$13,IF($B$1:$B$10=$B$13,1)),0),{3,4,5,6}) with CSE

Is the post useful?

Kindly put your comment in the comment section and subscribe the blog:)


Friday, 1 September 2017

FORMULA TO ASSIGN RANK (RANK FUNCTION ALTERNATIVE)



We have a in-built RANK function in excel to assign rank to our data but the issue with this function is it skips ranking(s) if there is a tie. In above case, it assigns 3 to 40 and 5 to 38 and skips ranking 4. But the formula that has been used in column C is a perfect substitute for it and returns the correct rankings even if there are duplicate values in our data. It can be used in place of excel RANK function.

Formula used in C2:C11

=SUM(0+(FREQUENCY(IF($A$2:$A$11>A2,$A$2:$A$11),$A$2:$A$11)>0))+1 with CSE

You can use "<" in place of  ">" if you want to assign the ranking in ascending order.

Thanks for reading it:)

Kindly put your valuable comment in the comment box and follow my blog.