Need Help with Formula Creation

Hi,

I am creating a metrics sheet for dashboard data. I need to create a formula that is using a reference sheet across two columns. One column in the source sheet is a name, but the second column is time. I want to do a (if name equals, then sum time for the name) formula.

If I wanted to put the formula is my source data sheet it would be =SUMIF([Researcher]:[Researcher], "FN", [Time spent | .1=6 minutes, .5=30 minutes]:[Time spent | .1=6 minutes, .5=30 minutes]).

Thank you in advance for any help.

Find more posts tagged with

Accepted answers

KPH

Hi @Felicia Nabors

1.Paste the formula that you have into the sheet you want it to be on (new sheet).

2.Then, click on the cell you put it in and highlight the text that is currently the column reference.

3.The pop up window will give you the option to Reference Another Sheet.

4.Click there and follow the instructions.

4a.You will select your source sheet from the tree.

4b.Then, highlight the Researcher column.

4c.Then give that column a name.

5.Once you click OK you will return to the new sheet and the text you highlighted in step 2 will be replaced with the cross sheet name you entered in step 4c. It will have { and } around it.

6. Repeat for the [Time... column.

There are some online help guides here:

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

All comments

KPH

Hi @Felicia Nabors

1.Paste the formula that you have into the sheet you want it to be on (new sheet).

2.Then, click on the cell you put it in and highlight the text that is currently the column reference.

3.The pop up window will give you the option to Reference Another Sheet.

4.Click there and follow the instructions.

4a.You will select your source sheet from the tree.

4b.Then, highlight the Researcher column.

4c.Then give that column a name.

5.Once you click OK you will return to the new sheet and the text you highlighted in step 2 will be replaced with the cross sheet name you entered in step 4c. It will have { and } around it.

6. Repeat for the [Time... column.

There are some online help guides here:

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Felicia Nabors

KPH thanks. When I tried that initially it failed for me, so I was obviously doing something wrong. It is working now.

KPH

Great! Enjoy your new dashboard when it's finished!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.