Search code examples
google-sheetsformulas

how to reference a formula from another cell in google sheets

background

i have a spreadsheet that has some advanced formulas.. for example i'm putting together the budget for the month and i have different employees who have different pay structures (ie some hourly, some hourly with bonus based on estimation target etc)..

I like to put a single formula and then copy and paste it across cells.. so this is what I have now

=if(C54="employee 1",D54*'resource information'!$D$3,
   if(C54="employee 2",D54*'resource information'!$D$8,
     if(C54="employee 3",D54*'resource information'!$D$4)))

which makes a basic multiplication of hours*hourly salary of each employee (employees 1/2/3 having different salaries)

problem

what if i would like to add an employee 4 who has a complex formula to extract their pay (ie with 3-4 different variables and extracted from different sheets?) i don't want to add that ugly formula to the above.. rather i would like to refer a cell that has that formula at the above.. something like this

=if(C54="employee 1",D54*'resource information'!$D$3,
   if(C54="employee 2",D54*'resource information'!$D$8,
     if(C54="employee 3",D54*'resource information'!$D$4))) 
 + employee 4 formula with parameters C54,D54 <-- this formula will be stored elsewhere

any idea?

Solution

  • Short answer

    In Google Sheets it's not possible to "hide" ugly formulas.

    Extended answer

    Don't worry too much about how a formula looks, worry about maintainability and readability. As alternative consider to create a custom function.

    Hint 1: Instead of nesting IF try a lookup function, like VLOOKUP.
    Hint 2: As you already figured out, you could nest a formula as argument of most functions just as you did with IF.

    Remark: Google Sheets formulas can't call formulas stored elsewhere, they can only call results of them.

    Example applying hints 1 and 2

    Assume that the resource information has in column A the resource ID (employee 1, employee 2, etc.)

    =D54*IF(
  C54<>"employee 4",
  VLOOKUP(C54,'resource information'!$A$3:$D$8,4,0),
  your_formula_goes_here
 )

    Hint 3: If you don't want to show your calculations for employee 4 in the formula bar, use a custom function.

    Remark: Google Apps Script can't call spreadsheet built-in functions but you could copy the JavaScript version from GitHub repositories like

    See my answer to another question where I share a spreadsheet with a bounded Google Apps Script project with JavaScript files from SocialCalc.

    See also