Module: TableTransform::FormulaHelper

Defined in:
lib/table_transform/formula_helper.rb

Overview

Help functions to create formulas

Class Method Summary collapse

Class Method Details

.column(name) ⇒ Object

Reference a column in same table



11
12
13
 
# File 'lib/table_transform/formula_helper.rb', line 11

def self.column(name)
  "[#{name}]"
end

.table(name) ⇒ Object

Reference a table



6
7
8
 
# File 'lib/table_transform/formula_helper.rb', line 6

def self.table(name)
  "#{name}[]"
end

.text(txt) ⇒ Object

Quotes text to be used inside formulas



16
17
18
 
# File 'lib/table_transform/formula_helper.rb', line 16

def self.text(txt)
  "\"#{txt}\""
end

.vlookup(search_value, table_name, return_col_name, default = nil) ⇒ Object

vlookup helper, search for a value in another table with return column specified by name Use other help functions to create an excel expression

Parameters:

  • search_value, (excel expression) —

    value to lookup

  • table_name, (string) —

    name of the table to search in

  • return_col_name, (string) —

    name of the return column in given table

  • default, (excel expression) —

    value if nothing was found, otherwise Excel will show N/A



27
28
29
30
31
32
33
34
 
# File 'lib/table_transform/formula_helper.rb', line 27

def self.vlookup(search_value, table_name, return_col_name, default = nil)
  vlookup = "VLOOKUP(#{search_value},#{table(table_name)},COLUMN(#{table_name}[[#Headers],#{column(return_col_name)}]),FALSE)"

  # Workaround
  # Should be possible to write "IFNA(#{vlookup},#{default})"
  # but Excel will error with #Name? until formula is updated by hand
  default.nil? ? vlookup : "IF(ISNA(#{vlookup}),#{default},#{vlookup})"
end