Circular Reference / In Excel it works / SOLVED

Oliver Lang

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)

 

Andrée Starå

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

Jacob

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.

Oliver Lang

SOLVED

 

Oliver Lang

Hi Andrée, we solved it. 

Thanks for your reply & have a nice weekend

Oliver Lang

Hi Jacob, 

this is solved now, thanks for your comment

have a nice weekend!

Andrée Starå

Excellent!

Happy to help!

Have a fantastic weekend!