Formula references - Referencing other tabs through auto lookups

Hello

Happy New Year everyone :-)


I have a workbook which needs to refer to a different tab each month. For example, last month, my formulas looked like this;

e.g. =COUNTIFS(INDIRECT($A4&C$1),$B4,'Nov20'!$CT:$CT,1,'Nov20'!$CU:$CU,1,'Nov20'!CV:CV,1)

I now need all to reference 'Dec20' rather than 'Nov 20' (there's a lot of different formulas and will take ages to manually change from 'Nov20' to 'Dec20', as well as possible human error in missing one).



I have made a look up table for it to refer to (e.g.you can see I've tried various different ways to reference it!).

SELECTION = data validation of the first column below. then columns 2, 3 or 4 pull through

Apr-20 "Apr20"
May-20 "May20"
Jun-20 "Jun20"
Jul-20 "Jul20"
Aug-20 "Aug20"
Sep-20 "sep20" Sep20 Sep20!
Oct-20 "Oct20" Oct20 Oct20!
Nov-20 "Nov20" Nov20 Nov20!
Dec-20 "Dec20" Dec20 Dec20!
Jan-21 "Jan21" Jan21 Jan21!
Feb-21 "Feb21" Feb21 Feb21!
Mar-21 "Mar21" Mar21 Mar21!


I've tried referencing it to the lookup in columns 2, 3 and 4 but I keep getting errors saying there is a problem with the formula.

Any help much appreciated!

Thanks
Emma

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

You'll need to use INDIRECT - in Calculator!B2:

=COUNTIFS(INDIRECT(TEXT(Admin!A1,"mmmyy")&"!B:B"),1,INDIRECT(TEXT(Admin!A1,"mmmyy")&"!C:C")

Correction:

=COUNTIFS(INDIRECT(TEXT(Admin!A1,"mmmyy")&"!B:B"),1,INDIRECT(TEXT(Admin!A1,"mmmyy")&"!C:C"),1)

Ali left a bit out at the end of her formula:

=COUNTIFS(INDIRECT(TEXT(Admin!A1,"mmmyy")&"!B:B"),1,INDIRECT(TEXT(Admin!A1,"mmmyy")&"!C:C"),1)

Note: if you refer to a sheet with a space in the tab name, this construction will not work. To be safe, use this, instead:

=COUNTIFS(INDIRECT("'"&TEXT(Admin!A1,"mmmyy")&"'!B:B"),1,INDIRECT("'"&TEXT(Admin!A1,"mmmyy")&"'!C:C"),1)

Otherwise, you may come adrift next time you try to use INDIRECT.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.