Google sheet - can I use a formula written in a cell as text?-CodePudding

I have a sheet called "data" with 10 columns. one of them is "Team Leader Name"

I created 7 sheets called as the team leaders first names "tom" "mike" "michael" ...

i wrote in each sheet the following formulas in B1

=GetSheetName()

and in A4

=QUERY(Data!$A$1:$J$18317;"SELECT WHERE J='"&B1&"'")

so in each sheet i can filter data for each team leader, while I update data in "Data" .

now i want to update the query, but I do not want to change it 7 times..

can I just write the formula in a cell, and then reference that as formula, so I can change it just once for all?

thanks!

CodePudding user response：

If I well understand what you mean, add a dummy parameter to each of =GetSheetName() for instance

=GetSheetName(Data!$L$1)

and put a checkbox in L1 of Data tab, even if this parameter is not called from the function GetSheetName. To update, you will only have to check or uncheck the checkbox.

CodePudding user response：

According to your explanation and spreadsheet, one solution is to use a script :

function rimpiazzare(txt){
  var str = txt.split('[')[1].split(']')[0]
  var val = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(str).getValue()
  return txt.replace('[' str ']',val)
}

then, in data!I3 you can write

SELECT * WHERE F='[B1]'

and in individual sheet

=QUERY(data!$A$1:$F$7; rimpiazzare(data!I3) )

https://docs.google.com/spreadsheets/d/1hPyytSYD_Hciq-Af70CGcIsuC_9Uvfff9ld9T4QIzm4/edit?usp=sharing