# Circular Reference / In Excel it works / SOLVED

✭✭✭
edited 12/09/19

This has been solved now

Solution is to take the part causing circular reference into a separate row, plus adding another row with a simple IF formula. So we ended up with two more rows, but its functioning and doing what it should. Saves some 30+people now a lot of work.

Hi there, we are stuck transforming some sheets from excel into Smartsheet.

The formula below works pretty well in excel, but in our Smartsheet is says "Circular Reference".

We elabortated the expression in Excel ">"&Month()"  might be the issue, but not clear on that. Any ideas how to solve this?

This this the expression from excel:

=IF(C\$1>MONTH(\$B\$3)+\$A29;IF(SUM(\$C27:\$N27)=0;0;(C27/SUMIF(\$C\$1:\$N\$1;">"&MONAT(\$B\$3)+\$A29;\$C27:\$N27)*(\$P29-SUMIF(\$C\$1:\$N\$1;"<"&MONAT(\$B\$3)+\$A29+1;\$C29:\$N29))));C28)

Tags:

• ✭✭✭✭✭✭

Hi Oliver,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• As you probably already know, a circular reference means the formula is looping back to itself, most likely thru another column.

I assume when the excel formulas were put in smartsheets, they weren't put in the same column they were in excel or when they were updated they were pointed to the wrong column.

• ✭✭✭

SOLVED

• ✭✭✭

Hi Andrée, we solved it.

• ✭✭✭

Hi Jacob,

this is solved now, thanks for your comment

have a nice weekend!

• ✭✭✭✭✭✭

Excellent!

Happy to help!

Have a fantastic weekend!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!