using "like" with cell to reference the last four digits in google sheets
hardCode formula dynamic formula I am using google sheets to make a report related to customers projects,
in my company When we have new project we give the project a 4 digits number then we create account receivable for the customer contain of 14 digits like that : the first 6 digits is fixed the second 4 digits represent the year and month the last 4 digits represent the project number
so i have column with the Project number and i want to make a query that return the count of costumer payment in front of the project number , when i try to use this formula :
=QUERY({'2016DB'!$A$2:$H$18168;'2018DB'!$A$2:$H$13379;'2017DB'!$A$2:$H$17759;'2019DB'!$A$2:$H},"select count(Col3) where Col1 = 12040118091492 And Col3 > 0 label count(Col3) '' ",0)
it's work fine
But when i try to make it dynamic by using like statement :
QUERY({'2016DB'!$A$2:$H$18168;'2018DB'!$A$2:$H$13379;'2017DB'!$A$2:$H$17759;'2019DB'!$A$2:$H},"select count(Col3) where Col1 like 120401____"""&E7&""" And Col3 > 0 label count(Col3) '' ",0)
Where E7 Cell Contain the Project number it doesn't work !!
Made a simple example with your type of query. Check this, please: https://docs.google.com/spreadsheets/d/1ISG0AjMNhwnXQvlxDJCMHcyx8mDKiCv6zqOfAGxrrDg/edit?usp=sharing (screenshot: https://i.sstatic.net/JOBuH.png)
Main formula is: =QUERY(A1:A5, "select A where A like 'a__"&D1&"'") where D1 is your needed project number. Looked up values are in format: letter a followed by 2 any characters and then project number.
Mind double and single quotes.
