Search code examples
google-sheetsgoogle-sheets-formulaformula

Update formulas from cell string

I have a sheet with a multitude of formulae for generating reports. Examples of the formulae are:

=countifs(Wk27_28_data!Z:Z,"no",Wk27_28_data!J:J,"<>Update",Wk27_28_data!J:J,"<>With Customer",Wk27_28_data!J:J,"<>In Progress",Wk27_28_data!C:C,"<>Change",Wk27_28_data!J:J,"<>With Supplier")

=Wk27_28_data!B5

=countifs(Wk27_28_data!D:D,A6,Wk27_28_data!C:C,"Automated Alert")

=COUNTIF(Wk27_28_data!F:F,A29)

Ideally what I want to be able to do is remove the 27_28 from the formulae and put it in to another cell which is then referenced by all the formulae, and thus I would be able to update them all just by changing this one cell. Does anyone know how I would accomplish this?

Solution

  • use:

    =COUNTIFS(INDIRECT("Wk"&A1&"_data!Z:Z"), "no",
          INDIRECT("Wk"&A1&"_data!J:J"), "<>Update",
          INDIRECT("Wk"&A1&"_data!J:J"), "<>With Customer",
          INDIRECT("Wk"&A1&"_data!J:J"), "<>In Progress",
          INDIRECT("Wk"&A1&"_data!C:C"), "<>Change",
          INDIRECT("Wk"&A1&"_data!J:J"), "<>With Supplier")

    =INDIRECT("Wk"&A1&"_data!B5")

    =COUNTIFS(INDIRECT("Wk"&A1&"_data!D:D"), A6,
          INDIRECT("Wk"&A1&"_data!C:C"), "Automated Alert")

    =COUNTIF(INDIRECT("Wk"&A1&"data!F:F"), A29)

    where A1 cell = 27_28