Circular Reference Error

I am trying to create a formula showing the utilisation of my Project Managers based on hours against projects and PTO vs. their full time contract hours. I am using the following formula for Project Manager 1:

=SUMIF(Person:Person, "PM1", Aug:Aug) / 140

When I apply the same formula for PM 2 (substituting PM1 name for PM2's) on the line below it then errors both formulas on both lines

I've tried using the AI tool, but that hasn't helped - I know it's going to be something really simple, but, I'm stumped!

TIA

Cheryl

Find more posts tagged with

Formulas

Accepted answers

All comments

Andrée Starå

https://community.smartsheet.com/discussion/141486/circular-reference-error

Hi,

I hope you're well and safe!

Please share some screenshots and/or additional information. (Delete/replace any confidential/sensitive information before sharing.) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

Cheryl C

This is the sheet. Hours against a project and PTO hours for each month are recorded and I want to work out the utilisation for each project manager against a 140 hour month. It works if I put the formula above in for one line (see the first row under current utilisation) but, as soon as I put the formula in for the 2nd PM on the second row I get a circular and blocked error (see above).

Andrée Starå

https://community.smartsheet.com/discussion/comment/491397#Comment_491397

Thanks!

Have you ensured that there aren't any error messages in the columns referenced?

✅ Remember! Did I help answer your questions or solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. It will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

Cheryl C

The screen shot shows all columns and there are no errors anywhere else as no other cells hold formulas and are just free text fields.

Thanks

Georgie

Hi @Cheryl C,

I set up a test sheet on my end that looks similar to yours, and found that the only times I was able to reproduce the errors you’re seeing are:

When I enter the formula =SUMIF(Person:Person, "PM1", Aug:Aug) / 140 into the Aug column, and the same formula but with “PM2” into the row below
When I enter the formula =SUMIF(Person:Person, "PM1", Jul:Jul) / 140 into the Jul column, and the same formula but with “PM2” into the row below

This is expected behaviour, since the circular reference error means that the formula references itself - you can’t place a formula that references a column into that same column.

From your description, it sounds like you’re referencing the Aug column in the formula in the Jul column, so this seems like unexpected behaviour. I’d recommend first confirming if the formula in the cells in which you’re seeing the error messages are referencing the same column. If they are, you’ll need to move your formula to a different column or change the column reference within the formula.

If the formulas are referencing a different column and you’re still getting the errors, please create a support ticket so that the team can investigate this fully.

Hope that helps!

Georgie

Cheryl C

Hi @Georgie

I'm referencing Jul:Jul in the July column for both formulas. I think what you are you saying the circular reference error IS expected behaviour in this case? If so, I'm assuming I have to create an additional column for each month just to get the average utilisation per PM? Thanks

Georgie

Hi @Cheryl C ,

Thanks for confirming - yes, that’s right, the errors are expected when you reference the same column in which you are placing the formula. So the solution would either be to create additional columns for each month, or a better solution may be to use the sheet summary and create your formulas there for each month and each PM.

As an example, see the sheet below - the sheet summary is open in the panel on the right hand side, and each field contains the relevant formula for the field:

You can find more information about sheet summaries here: Sheet Summary

Does that work for you?

Georgie

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.