Search code examples
google-sheets

How to call cell value within concatenate function in google sheets?

I have prepared a sheet pic attached

In cell B1, enter a formula

>0

In the cell CF2, entered a formula

=IFS(B1<>"", " AND Col21  "&B1&" " , B1 = "", "")

and the result is AND Col21 >0

In cell CD2, I have entered the formula

=IFS(CONCATENATE( CA2, "1")<>"", " AND "&CC2&"  "&CONCATENATE( CA2, "1")&" " , CONCATENATE( CA2, "1") = "", "")

and the output is AND Col21 B1

But, I want to be desired output as AND Col21 >0

In other words, I need to call the value of the cell B1 instead of the Name B1.

Solution

  • You may try n' wrap the CONCATENATE part of the formula within INDIRECT()

    =let(cell_,indirect(CA2&"1"),
     ifs(cell_<>"", " AND "&CC2&"  "&cell_&" " , cell_="",""))