Mixed Sheet Structure

I have a Sheet where users enter tickets for Enhancements to current applications. These could be any type of requests and there are several steps necessary prior to approval to start work. There are two types of enhancements that when approved are 'tracked' on a different log, though specific fields updating the primary Simplification, Enhancement & Optimization (SEO) log. My challenge is how to manage the "If" when the criteria is met. The three examples are:

If Request Type = Report, Dashboard, Archive then create link in Reports & Analytics Log fields to update in SEO are %Complete, Status, Actual Start, Actual Finish, Assigned Resources (The Reports & Analytics Log adopts several fields from SEO, currently this is done via the unique number on each log with a simple lookup)

If Request Type = Enhancement and ETC > 80 then create link to SEO Backlog. The field adoption is similar as above.

The challenge I have is twofold. Creating a calculation for the SEO log that is stable and doesn't need to be added each time we add the new Reports or Backlog unique number to the master sheet. There is an expectation that the calculations will be overwritten when the criteria are not met, but how to have the calculation be part of a new row when a new request added?

Find more posts tagged with

Links

field calculation

lookup

Accepted answers

All comments

Georgie

Hi @LDLValentine,

I’m not sure I fully understand your query, but would converting the formula to a column formula work?

If not, or if you’d like some help with creating the formula, would you be able to share screenshots showing the setup of both sheets (with any sensitive data removed/hidden) and a little more detail about what you’re looking to achieve with the formula, and where you want it to pull data from/to?

Thanks,

Georgie

LDLValentine

Thanks for the response. Let me try to provide context. Providing screen prints would be nearly impossible as each of the sheets in question are very large (+35 active columns).

At this time, all formulas are column formulas. I am trying to find a way to stabilize the formulas in the primary sheet (SEO). There are several shared fields between three (3) sheets, the key is the SEO No at the row. Similarly, at each of the supporting sheets there is an Item No at the row to cross reference.

I am using a IFERROR(VLOOKUP calculation, and it works fine in all cases but one. This is not the problem I am having.

The SEO current has 200+ entries.

Of these 37 are Reports & Analytics, thus part a separate sheet for development tracking but status is part of update through the Lookup.

Additionally, of the 200+, 7 are currently part of the >80 hours that are tracked on the Backlog and once again is part of the status update through the Lookup.

On the separate sheets, all the calculations are working perfectly and are not an issue.

On the SEO, I need a way to prevent manually overwriting calculations once they are set in a field, on a row.

KPH

I am also not sure I understand the question. There are two things I think it could be.

1 - locking rows

See

https://help.smartsheet.com/articles/522077-locking-unlocking-columns-and-rows

2 - formulas with overrides

You can create a column formula to do a calculation only if another column that is used for manual override is blank. That way you can allow users to enter data manually and override the calculated value without editing the formula and impacting other rows.

I hope one of those things is along the right lines.

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