EXCEL.txt

### Calculate the growth of 2 values
(newval - oldval) / oldVal * 100


### Custom Format for Date and Time
JJJJ-MM-TT hh:mm
JJJJ-MM-TT hh:mm:ss


### Custom Format for show value in Thousands / millions
#.##0.    => show value in thousand
#.##0..   => show value in millions


### How to make a lookup to some other information
=VLOOKUP(A8;Admin!$A$3:$C$100;2;FALSE)      # english version
=SVERWEIS(A8;Admin!$A$3:$C$100;2;FALSE)     # german version
A8: key-value what should be checked
Admin!$A$3:$F$100: area where the detail informations are stored - key-value must be in first column
2: which column should be taken - in that case the second column of the above list
FALSE: default value when the key-value is not found


### How to create a dependant dropdownbox
see: C:\Users\Polzi\Documents\DEV\Learning-Documentation\Excel_HowToDependent_Drop_Down_EveryRow.xlsx
=OFFSET(Work!$A$1;1;MATCH($B2;Work!$A$1:$Z$1;0)-1;COUNTA(OFFSET(Work!$A$1;1;MATCH($B2;Work!$A$1:$Z$1;0)-1;20));1)
Work!$A$1: erste Überschrift der Abhängigkeitsdaten der 2.Auswahl
$B2 => Auswahlelement, welche geprüft werden soll
Work!$A$1:$Z$1 => alle Überschriften, der 2.Auswahl


### How to call a python-program in excel with VBA
create button: Insert - Shapes
assign macro: right click - Makro zuweisen
macro for calling the python program:
Sub init()
    Dim FN As String
    FN = Application.ThisWorkbook.Path
    FN = FN + "\initData.exe"
    Shell FN, vbNormalFocus
End Sub
for testing purposes probably add
    MsgBox "The value of FN is " & FN, vbInformation
with that the path to the exe-file will be outputted in an msg-window


### Check cell-value if MsgBox should be outputed
If ThisWorkbook.Sheets("PARAM").Range("B4").Value = "y" then
  MsgBox "The value of FN is " & FN, vbInformation  
End IF


### Create a filter list without duplicates and empty cells
see: Excel_HowtoDropBoxWithoutBlanks.xlsx
- define input-values in eg. column a
- copy this formula in eg. column c:
  =IFERROR(INDEX($A$1:$A$100; MATCH(0; COUNTIF($C$1:C1; $A$1:$A$100&"") + IF($A$1:$A$100="";1;0); 0)); "")
- define List-Name in Formulas => Name Manager: eg.
  =OFFSET(Tabelle1!$C$2;;;COUNTIF(Tabelle1!$C$2:$C$100;"> ");1)
- define List-Element in Data => Data Validation => List and reference to List-Name from above: eg. 
  =LIST


### Delete empty cells / rows
- select empty cell
- STRG G und Inhalte...
- select Leerzeilen / empty rows
- STRG -
- select shift cells up


### How to read a csv-file with the best results in excel
- Data > Get Data > From File > From Text/CSV
- import the file using the UTF-8 Format with the "comma as delimiter"


### How to change the column-headers from char to number
- File - Options - Formulas
- check R1C1 reference style (under Working with formulas)


### Force specific delimiter when open a csv in excel
put "sep=;" in the first line - then the ; delimiter will be used
doesn´t matter which language you are using
https://superuser.com/questions/1145892/how-to-make-excel-open-csv-files-and-automatically-split-the-comma-delimited-col