module TableTransform::FormulaHelper

Help functions to create formulas

Public Class Methods

column(name) click to toggle source

Reference a column in same table

# File lib/table_transform/formula_helper.rb, line 11
def self.column(name)
  "[#{name}]"
end
table(name) click to toggle source

Reference a table

# File lib/table_transform/formula_helper.rb, line 6
def self.table(name)
  "#{name}[]"
end
text(txt) click to toggle source

Quotes text to be used inside formulas

# 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) click to toggle source

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

@param [excel expression] search_value, value to lookup @param [string] table_name, name of the table to search in @param [string] return_col_name, name of the return column in given table @param [excel expression] default, value if nothing was found, otherwise Excel will show N/A

# 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