Over ride add on signing in firefox,integrate IDM with firefox

You can temporarily override the setting to enforce the add-on signing requirement by changing the preference xpinstall.signatures.required to false in the Firefox Configuration Editor (about:config page).

source:https://support.mozilla.org/en-US/kb/add-on-signing-in-firefox?as=u&utm_source=inproduct

Filter VBA Project,Sourav Bhattacharya,Excel VBA faculty,Kolkata

Please send me a message to view the file,it's copyrighted
Google sheet does not support this,so you have to download this and see in excel to understand it.


Sourav Bhattacharya
Excel VBA Faculty

Final automated summary report after ytd and ytd growth calculation added

Three subs are added

 
Sub ytdcal(temppos As String)

Dim exampleDate As Date

Dim i As Integer


Dim str2 As String
Dim result As Long
result = 0

 Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
Sheets("Summary").Select


If InStr((Range("$I$1").Value), "16") <> 0 Then
'MsgBox ("true")
exampleDate = DateValue(Range("$I$1").Value)
result = 0

For i = 4 To Month(exampleDate)

Range(temppos).Select
    ActiveCell.Offset(0, 8).Select

 ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT( " & Month(exampleDate) + (4 - i) & " *28,""mmm"") & ""-"" & TEXT(R1C9,""yy""),X!R4C4:R125C39,MATCH( " & str2 & ",X!R5C3:R125C3,0)+1,FALSE),0)"
        result = result + CLng(ActiveCell.Value)
       
        Next i

'MsgBox (result)
 Range(temppos).Select
    ActiveCell.Offset(0, 8).Select
        ActiveCell.Value = result

'MsgBox (result)


Else
result = 0
exampleDate = DateValue(Range("$I$1").Value)

For i = 0 To (8 + Month(exampleDate))
Range(temppos).Select
    ActiveCell.Offset(0, 8).Select
  ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )       
        result = result + CLng(ActiveCell.Value)
       
        Next i
        Range(temppos).Select
    ActiveCell.Offset(0, 8).Select
        ActiveCell.Value = result
       
      '  MsgBox (result)
End If




End Sub



Sub ytdcom(temppos As String)

Dim exampleDate As Date

Dim i As Integer


Dim str2 As String
Dim result1 As Long
Dim result2 As Long

result1 = 0
result2 = 0


 Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
Sheets("Summary").Select
Range(temppos).Select
    ActiveCell.Offset(0, 8).Select
    result1 = ActiveCell.Value
   


'MsgBox ("true")
exampleDate = DateValue(Range("$I$1").Value)

'MsgBox (Year(exampleDate))

If (Year(exampleDate) - 1) = 2015 Then

exampleDate = DateValue(Range("$I$1").Value)
result = 0

For i = 4 To Month(exampleDate)

Range(temppos).Select
    ActiveCell.Offset(0, 11).Select

 ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT( " & Month(exampleDate) + (4 - i) & " *28,""mmm"") & ""-"" & TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH( " & str2 & ",X!R5C3:R125C3,0)+1,FALSE),0)"
        result2 = result2 + CLng(ActiveCell.Value)
       
        Next i
Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
        ActiveCell.Value = result2

Else
exampleDate = DateValue(Range("$I$1").Value)

For i = 0 To (8 + Month(exampleDate))
Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
  ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )       
        result2 = result2 + CLng(ActiveCell.Value)
       
        Next i
        Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
        ActiveCell.Value = result2
       

End If
 Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
    On Error Resume Next
   
 ActiveCell.Value = (result1 / result2) - 1


      '  MsgBox (result)





End Sub



Sub ytdcomtotal(temppos As String)

Dim exampleDate As Date

Dim i As Integer


Dim str2 As String
Dim result1 As Long
Dim result2 As Long

result1 = 0
result2 = 0


 Range(temppos).Select
str2 = """" & ActiveCell.Value & """"
Sheets("Summary").Select
Range(temppos).Select
    ActiveCell.Offset(0, 8).Select
    result1 = ActiveCell.Value
   


'MsgBox ("true")
exampleDate = DateValue(Range("$I$1").Value)

'MsgBox (Year(exampleDate))

If (Year(exampleDate) - 1) = 2015 Then

exampleDate = DateValue(Range("$I$1").Value)
result = 0

For i = 4 To Month(exampleDate)

Range(temppos).Select
    ActiveCell.Offset(0, 11).Select

 ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT( " & Month(exampleDate) + (4 - i) & " *28,""mmm"") & ""-"" & TEXT(R1C9,""yy"")-1,X!R4C4:R125C39,MATCH( " & str2 & ",X!R5C2:R125C2,0)+1,FALSE),0)"
        result2 = result2 + CLng(ActiveCell.Value)
       
        Next i
Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
        ActiveCell.Value = result2

Else
exampleDate = DateValue(Range("$I$1").Value)

For i = 0 To (8 + Month(exampleDate))
Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
  ActiveCell.FormulaR1C1 = _
        "=IFERROR(HLOOKUP(TEXT((IF((TEXT(R1C9,""mm"")- " & i & " )       
        result2 = result2 + CLng(ActiveCell.Value)
       
        Next i
        Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
        ActiveCell.Value = result2
       

End If
 Range(temppos).Select
    ActiveCell.Offset(0, 11).Select
    On Error Resume Next
   
 ActiveCell.Value = (result1 / result2) - 1


      '  MsgBox (result)





End Sub









Sourav Bhattacharya
Excel Vba Teacher 
919748184075

Use a variable value from cell in Countif,Sourav Bhattacharya,Excel Faculty(91-9748184075)

The formula was

=COUNTIFS(INDIRECT("'"&$A$2&"'!$A$3:$A$1048576"),'Dlr wise Tier Performance(K)'!$A$4,INDIRECT("'"&$A$2&"'!$E$3:$E$1048576"),'Dlr wise Tier Performance(K)'!$B$4,INDIRECT("'"&$A$2&"'!M3:M1048576"),"<>")

where A2 cell has the changing value

See the file

Sourav Bhattacharya

Advanced Excel Faculty

Kolkata

91-9748184075

Interesting date calculation formula for my student

So I have a issue like this

Jan-15	12
Feb-15	12
Mar-15	12
Apr-15	12
May-15	11
Jun-15	10
Jul-15	9
Aug-15	8
Sep-15	7
Oct-15	6
Nov-15	5
Dec-15	4
Jan-16	3
Feb-16	2
Mar-16	1
Apr-16	0
May-16	0

The left side data will be coming from the formula ,example if an employee has the joining on Jan 15 the formula will give his employment time as 12 month



The reference date for this is 4/1/2016
which is in N2







On G2 I have a date 3/24/2015(I have month/date/year format)


On I2 I should write this

=IF(YEAR(G2)=YEAR($N$2),MONTH($N$2)-MONTH(G2),(YEAR($N$2)-YEAR(G2))*12+(MONTH($N$2)-MONTH(G2)))

and on H2 I have to write this


=IF(I2>=12,12,IF(I2>=11,11,IF(I2>=10,10,IF(I2>=9,9,IF(I2>=8,8,IF(I2>=7,7,IF(I2>=6,6,IF(I2>=5,5,IF(I2>=4,4,IF(I2>=3,3,IF(I2>=2,2,IF(I2>=1,1,IF(I2>=0,0,IF(I2>=-1,0))))))))))))))












voila problem solved







I have added the excel file







but then the student said the formula to wrapped in one column

so

the formula ended up like this


=IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=12,12,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=11,11,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=10,10,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=9,9,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=8,8,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=7,7,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=6,6,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=5,5,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=4,4,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=3,3,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=2,2,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=1,1,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=0,0,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=-1,0,0))))))))))))))





This is the final file


Sourav Bhattacharya
Excel/Excel VBA Teacher 
Mail me at allsourav2atgmaildotcom