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

I created 7 sheets called 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 a formula, so I can change it just once for all?

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 sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var regex = /\[[A-Z0-9]{2,}\]/g
  txt.match(regex).forEach(function(ad){
    try{
      var val = sh.getRange(ad.replace(/\[|\]/g,'')).getValue()
      txt = txt.replace(ad,val)
    }catch(e){}
  })
  return txt
}

then, in data!I3 you can write

SELECT * WHERE F='[B1]'

and in individual sheet

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

take a copy of the spreadsheet here

edit

Note that to reactivate formulas when loading the spreadsheet, you will need to add a dummy parameter and change the value by onOpen

function onOpen(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data')
  // to re-activate the formulas
  sh.getRange('J3').setValue(!sh.getRange('J3').getValue())
}

that means also to add this dummy parameter to the function

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