I am trying to write an “IF” statement in an Adagio Fx worksheet that would look at the account cell and utilize the text within the account cell to create a calculation -the worksheet is a smartsheet, and when it is rolled down the accounts show up in the format
4000- 30_500
The leading spaces are created within Adagio Fx, and the account code consists of the Account # (4000), the department mask (30) and the sub account code (500).
I want the IF statement to make a calculation based on the dept. mask (ie 30, 20, 10) – in the calculation, D27 = base amount, E8 is a fixed conversion rate, and B27 is where the account code show above is.
I’ve tried getting this calculation in a cell on the rolled up sheet as follows;
IF(ISERROR(FIND(“30_”,B27,1))==”TRUE”,D27*E$8,D27)
And also
IF(ISERROR(FIND(“30_???”,B27,1))==”TRUE”,D27*E$8,D27)
In all cases, when the worksheet rolls down, although there are “30_??? Accounts, it does not perform the calculation required, which is to multiply the base (d27) times the exchange rate (E8), or if not dept 30, then to return the base amount in the cell.
I can insert a new column, and use a text string such as aaaabbb30_cccc, and then a text string aaaabbbbcccc (no 30) and re-reference the formula to these test names, and the formula then works.
Can anyone help me as to why the formula cannot find the “30” accounts in the account code? I have also tried ensuring that the account code column was formatted as text so that the formula “30” should find it. I am wondering if it is an issue related to the roll-down on the sheet.

You posted this question in the Technical Support Forum. Please only post questions in one Forum.

Sorry, meant to post in general