Update formulas from cell string

user_14959616 0
Viewed 44

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?

google-sheets-formula formula google-sheets
2 Answers

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

user_56326290

Assuming Wk27_28 text is entered in cell A1. Replace

=Wk27_28_data!B5

with

=INDIRECT("Wk"&$A$1&"_data!B5",TRUE)

and do the same for others.

Please share if it works/understandable/not.

user_76024680
Related