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