Please Help

kcain

Hi, Smartsheet friends.  I am new to Smartsheet and am still trying to find my way around.  Is it possible to calculate a formula on one sheet that references data on a separate sheet, like you can in Excel?

 

I have a master sheet that includes all my data.  I tried to summarize the data using various calculations at the bottom of the sheet, but some of the numeric formulas are not working because they are located in date columns.  There are a lot of date columns in the master sheet, so it isn't feasible to move the calculation to a different column.

 

Can someone please give me advice on how to resolve this issue?  Any help is much appreciated.

 

Thanks!

J. Craig Williams

For the first question - can a formula reference another sheet?

Not yet.

They are working on it.

 

In the meantime, if you can link the data cells and pull them to one sheet, that is the best option.

 

For the second question, putting calculations in Date columns:

Use + "" 

For example, this formula

=COUNTIF(CHILDREN(), DATE(2016, 12, 4)) + ""

would count the number of children with the date of 2016-12-04.

 

Watch out of ciruclar references wehn builiding formulas that reference their own columns.

 

Hope this helps

 

Craig

 

 

 

 

 

Richard Rymill SBP

Well said Craig

to expand on the design, you need to do all your calculations in the sheet you are in, then cell link the results to your master sheet. 

 

hope that helps

RichardR

kcain

Craig,

 

I really appreciate your prompt response.  Is there a way I can share the subject sheet with you so that I can see exactly what you mean by the DATE formula?

 

 

Thanks!

J. Craig Williams

Yes.

 

Share to jcwill23@gmail.com

 

Craig

J. Craig Williams

I'm in the sheet.

 

I changed the formula in the 'Under MGMT' section to:

 

=COUNTIFS(AVP1:AVP57, [FHA #]64, STATUS1:STATUS57, "IE/Under Constr") + ""

 

Note that the critieria (shown here as [FHA #]64 was a name listed in the AVP and each formula in this section was different. Now it just needs to be copied, not edited, if someone else is added to this section.

I also added the + "" for converting to text.

 

I did similar to the "In Pipeline' section.

 

Please check to see if the results are as you expected.

 

Craig

 

 

kcain

Craig,

 

For some reason, when I try to sum the total number of properties in the "IE/Under Constr" column (cell 69 under MGMT EFFECTIVE DATE), it gives me an "Incorrect Argument" message.  How can I correct this?

 

Also,  the main reason I have the summary calculations at the bottom of the "Copy of Corporate Profile" sheet is so that I can add those metrics to a Sight (I just learned that I have to have the metrics pre-calculated before they can be added to the Sight.  As an Excel user, this seems strange to me.  I was hoping I could use the Sight almost like I would if I were creating an Excel PivotTable.  Anyway, since this sheet will be a living document that will frequently have new rows and properties added, do you recommend I keep the summary tabulations at the bottom of the master sheet or on another sheet?  If you recommend linking the calculations to another sheet, can you please show me how?  I have never used the cell linking feature.

 

Thank you so much for your wealth of knowledge.  I cannot wait to eventually learn all of the special features of this system!!

J. Craig Williams

If you are trying to sum the count - remember the +"" converted the dates to text and the column is a date type -- 

 

so you'll need to convert the text to numbers (using the VALUE()) function and then convert them back using the +""

 

You'll need to reshare the sheet with me for me to do more than just thinking off the top of my head.

 

I would probably keep the info in the sheet instead of linking it out to another.

 

Craig

 

J. Craig Williams

The more I think about it - trying to sum a bunch of text in a date column is just torturing yourself.

I doubt the SUM and @cell will help.

 

But ... you could get the value in a different cell (maybe way off to the right) and sum those ... and then bring it back to the column you want to display it with a simple equate.

 

1. Each row - add =VALUE([MGMT EFFECTIVE DATE]64) in some unused (for these rows) column.

2. In the total run, in that column =SUM( -- the proper reference )

3. In [MGMT EFFECTIVE DATE]68 

= [that column]69 + ""

 

Craig

 

Craig

J. Craig Williams

Sheet was shared to me after I wrote that.

But that was what I did.

 

Craig

J. Craig Williams

A few comments on the final tweaks to Kelly's development:

 

The sheet contains a series of columns of different types.

At the bottom, she's consolidating and counting and summing - but for visual ease of use, the column usage may not correspond between data and analysis area.

For example, a date column is used to sum the count of active projects by user below.

 

For Text/Number columns, count and sum formulas work fine.

For Date and Checkbox columns, trying to use them counts and sums requires a bit more coding.

To have what appears to be a number in these two column types requires forcing the result to text - we do this by concatenating with something that is text. An empty text item ("") is often the choice.

 

In a text/number field:

 

2  is a number

2 + "" is text

 

Converting the result to text allows the result to be displayed -- but it can't be used in calculations directly. To do that, we convert it back to a number using the VALUE() function. 

But the rules of what result can be displayed in the Date and Checkbox columns don't change.

To add two text results and display in a check box, something like this is used.

 

=VALUE(cell1) + VALUE(cell2) + ""

 

Depending on sheet maintenance, I would usually put those VALUE's somewhere else (like I discussed above) and then perform calculations in that somewhere else.

But it can't also be done in the Checkbox and Date column.

 

The sheet also had a column of check boxes in which the % checked was needed.

That formula is 

 

=(COUNTIF( checkboxrange, 1) / COUNT (checkboxrange))*100 + ""

or

=(COUNTIF( checkboxrange, 1) / COUNT (checkboxrange))*100 + "%"

 

For larger numbers of check boxes, the results can be cleaned up using either the LEFT or ROUND functions.

For example

=LEFT((COUNTIF(Chkbox18:Chkbox26, 1) / COUNT(Chkbox18:Chkbox26)) * 100 + "", 5) + "%"

or

=ROUND((COUNTIF(Chkbox18:Chkbox26, 1) / COUNT(Chkbox18:Chkbox26)) * 100, 2) + "%"

 

Hope that helps someone.

 

Craig

kcain

Craig,

 

Thanks again for all your help; you have been extremely informative.  I have another question that has already been posted on the message board, but for which I did not receive an answer.

 

Thank you!

kcain

Sorry - I totally forgot to post the question.  Please see below:

 

I am creating a Sight that will include various metrics for four different states.  I have four PNG files with icons of each state shape.  What I would like to do is have the data for a particular state appear on top of the image for that state.  Is there a way to overlap the two widgets?

J. Craig Williams

You are welcome.

It looks like Shaine answered your question in the other thread.

 

Craig