Circular Reference / In Excel it works / SOLVED
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)
Comments
-
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.
Thanks for your reply & have a nice weekend
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!