Excel 2016 - Office 2016 - Excel 2016 versiyon is coming

´Excel Power Query

´New forecast functions

´Pivot table, something new in slicers

´Pivot charts, time gruping

´PowerView for OLAP connections 

´Data model, PivotTable, use relationships automatically

´New BI functions and capabilities

´Excel data cards 

´……

VLOOKUP function - 15 different usage scenarios

VLOOKUP function is one of the most powerfull function in Excel.

We can explain 15 different usage for you. 

1. Exact match (typical scenario). 

2. Close match (for numbers)

3. Wildcard characters  (* and ?)

4. With error control (iferror). 

5. With error control (if  iserror)

6. Compare two lists

7. With MATHC function. Two dimensional.

8. With ROW function. 

9. Winth COLUNN function. 

10. With IF function. 

11. With LOOKUP function. 

12. With CHOOSE function. 

13. Winth SUM function. And Array usage.

14. With INDIRECT function. 

15. Other usages.

userform validation

' textbox must be filled

If txtAdiSoyadi.Text = "" Then
   MsgBox " name textbox must be filled!!!!!!!!!"
   txtAdiSoyadi.SetFocus
   Exit Sub
 
End If

If txtTcKimlikNo.Text = "" Then
   MsgBox "ID textbox must be filled!!!!!!!!"
   txtTcKimlikNo.SetFocus
   Exit Sub
End If

EXCEL VBA - how many rows data we are going to handle in loop?

Range("a" & Rows.Count).End(xlUp).Row


MsgBox (Range("a" & Rows.Count).End(xlUp).Row)
          ' from bottom to the last filled cell

     
With Cells :
 

MsgBox (Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row)

INDEX function simplified

Excel shortcut keys: Ctrl and 8

Excel Shortcut Keys – Ctrl and 8

To hide and display the outline symbols, press Ctrl+8

To restore the outline symbols again, press Ctrl+8 

Note: First use Data / Groups tool on typical data and then use these keys to hide and display grouping symbols. 

Using Smiley icon in Excel

Using Smiley icon in Excel is one of the most asked questions. 

First of all, is is a character like J,K,L. But is Windings font. 

You should build your IF sentences accordingly. Then create charater with CHAR function. 

Such as; IF (a1>b2;CHAR(74);.......).  Then format the cells where smileys are seen with Windings.