First Last

First Last

  • 849
  • 648
  • 67.9k

How to build a formula on values and operators defined in cells

Jul 16 2023 3:30 PM

I have a formula in cell (in the green box below) referencing a cell in the current sheet (C42) [not shown] and other cells [B134 and D134] in another sheet.

The cells formula:

=IF(AND(C42>'Common For All'!B134,(C42<'Common For All'!D134)),'Common For All'!H134,0)

I just want to build the same formula above using the references to the comparison operators in cols A and C (in the other sheet) instead of hardcoding the comparison operators as they may change. And I would then just change the tables comparison operators and hence the cell will perform dynamically.

The other sheet:

Just not sure how to string them into the formula.

I tried:

=IF(AND(C42&" "&TEXTJOIN(" ",TRUE,'Common For All'!A134:'Common For All'!B134),(C42&" "&TEXTJOIN(" ",TRUE,'Common For All'!C134:'Common For All'!D134))),'Common For All'!H134,0)

It looks like it resolves it, but then fails.

Answers (2)