Forum Discussion

HansVogelaar's avatar
HansVogelaar
MVP
Jan 31, 2023

Re: Delete hidden quotations from concat formula

its_the_wil 

I suspect that INDIRECT would do what you want, but:

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

4 Replies

  • its_the_wil's avatar
    its_the_wil
    Copper Contributor
    Feb 01, 2023
    =COUNTIFS(StaffTBL[Position],"Head Teacher",StaffTBL[Gender],"MALE",StaffTBL[Dec-2022],"Active")

    StaffTBL: Table Name

    Position, Gender, Dec-2022: Columns Name

    i want to change the selection of the month column according to the current month ..so , i have column for months (Sep-2022,Oct-2022,.....Jul-2023) , i  used countifs as shown above and i tried to change month column dynamically by using Hlookup formula inside countifs formula , and it worked ,but when the Hlookup brings column name  it keeps put hidden quotes ("" "") at first and end of column name, if U use hlookup separately it will bring correct column name but when i put it inside countifs it brings column name as  "StaffTBL[Dec-2022]"
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Feb 02, 2023

      its_the_wil 

      Does this do what you want?

       

      =COUNTIFS(StaffTBL[Position],"Head Teacher",StaffTBL[Gender],"MALE",INDIRECT("StaffTBL["&TEXT(TODAY(),"mmm-yyyy")&"]"),"Active")