Linking Cell Values From a Cell With a Formula

I am trying to create a workaround using the cell linking function in order to avoid circular references. In the picture below, the Premium column contains formulas calculating the appropriate total. Is there a way to have the Bound Premium column show only the value from the Premium column without carrying over the formula?

Smartsheet Screenshot.1jpg.jpg

Find more posts tagged with

Sales

Finance

Comments

Atus Bartal

Brian,

Why do you use cell linking and why not just "=Premium2" (in Bound Premium column, where 2 is line#)? You say "in order to avoid circular references" - why is this a problem?

I don't really understand the way you use cell linking in this sheet either, since it works only between different sheets. Is there another sheet you have for linking to and from?

Atus

Brian T.

Atus,

Thanks for the reply. When i put "=Premium2" it voids the formula in "Premium2" because the SUMIF range is the entire "Bound Premium" column, thus creating a circular reference. In attempting to work around this, I created a 2nd sheet where I could link the desired total, hoping it would only pull the value instead of the formula. I then planned to link the value back to the desired cell in the 1st sheet. Unfortunately the link pulls the formula with it and still creates the circular reference.

This brings up a second question... Why would it create a circular reference when the cell does not meet the SUMIF criteria?

Brian

Travis

SUMIFs will show circular reference if you tried to reference itself, regardless of if it meets the condition.

You want to SUMIF the entire Bound Premium column and show the total in Premium and Bound Premium, correct?

What if you added the total to the Prospect Name cell where it says 2015 TOTAL?

Heres what you could do in the Prospect Name column, the 2015 TOTAL cell (I will assume this is row 2)

="2015 TOTAL: " + Premium2

This will show:

2015 TOTAL: $34,650

Brian T.

Travis, your suggestion is a great one and I may have to resort to that. My problem is I want to display totals for 3 different columns in a single row.

Here is a better sheet example of what I am trying to accomplish.

I want to SUMIF the highlighted columns and show the total in those same columns. The ultimate goal is to create a report that displays SALESPERSONS' clients closed in 2015 (I have a hidden column with the close date) and include the 2015 TOTAL row displaying at the bottom of the report. Currently the information is being entered into the original sheet via Web Form, which is why I want to reference the entire column. I may just need to resort to making a parent row and SUMIF the children...

Thanks for the input Travis.

Smartsheet Screenshot2.jpg

Travis

SUMIF(CHILDREN() might be a good route to go if you want the SUMIFS to exist in the same column as the values. It would just require you to indent the new rows when they are submitted through the web form.