Jan 20 2020 05:11 PM
I've constructed a cell range using concatenate which works fine except that when it is returned, Excel sticks double quotes around them and my match function fails.
I've tried trim&substitute. I've tried putting the function into a cell and removing the function to make it the actual value. nothing works.
Any suggestions please?
e.g. Concatenate("D","35",":","D","42") is displayed as D35:D42 in excel but returns "D35:D42" as the parameter of Match.
Jan 20 2020 05:36 PM - edited Jan 20 2020 05:37 PM
Not sure about this, but it looks to me as if what might want is =INDIRECT("D35:D42")
Try that. If it's not what you're seeking, come back with an example spreadsheet and a more complete description.
Jan 20 2020 05:55 PM
Jan 31 2023 01:13 AM
Jan 31 2023 09:49 AM
hi , i have the same problem, i couldn't solve it with indirect !!
any suggestions plz ?
It's probably not the same problem then, wouldn't you think? So let's start with a more complete description of whatever the problem is that you're experiencing. And, if it's possible--if your spreadsheet doesn't contain confidential or proprietary info--please post a copy of the spreadsheet itself in OneDrive or GoogleDrive and paste a link here that grants edit access.
Feb 01 2023 11:55 PM
=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]"
Feb 02 2023 10:09 AM
=COUNTIFS(StaffTBL[Position],"Head Teacher",StaffTBL[Gender],"MALE",StaffTBL[Dec-2022],"Active")
<snip>...</snip>
i used countifs as shown above and i tried to change month column dynamically by using Hlookup formula inside countifs formula
There is no HLOOKUP evident in that COUNTIFS formula.
So I repeat: if it's possible--if your spreadsheet doesn't contain confidential or proprietary info--please post a copy of the spreadsheet itself in OneDrive or GoogleDrive and paste a link here that grants edit access.
Let me expand on that: if your actual spreadsheet does contain confidential or proprietary info, then create a copy that uses names of Star Wars or Disney characters, but still is representative of your actual. And post that mockup in OneDrive or GoogleDrive and paste a link here that grants edit access.
Feb 02 2023 11:42 AM