Create an Evaluation Tool

Hi Friends,

I am new to smartsheet, please be aware of that, likely some of my struggles are obsolete but I just have no idea.

Problem Statement:

On global business level, we created a form for employees to forward improvement Ideas to a specific team that will execute them (or not). The form entries are logged in a smartsheet with columns for each data entry in the form. Here a sample screenshot. I call it MASTER

One of the columns, namely SITE, specifies, for which local business an entry is relevant. Lets call my relevant entries mysite.

What I dream of:

I want to evaluate mysite entries based on local business criteria (so I cannot add evaluation criteria to the MASTER file, as this is a global source sheet)

(8 criteria categories, each category has a number value and the sum of all yields a final score).

Ideally I have an evaluation tool/form with mysite MASTER entries ready to be evaluated. In my head that was a CHILD sheet with copied entries from MASTER with extra pre-formatted columns for evaluation criteria and score, but so far no success.

What I tried and where I struggled:

  1. Copy row workflow (MASTER sheet to CHILD sheet)
  • This resulted in several entries being double. When a change is made in MASTER, the change triggers a new entry in CHILD and not changing the specific cells that changed --> i call it a changelog file and this is not what I wanted. I wanted to see what basically is the MASTER with mysite filter permanent, but as a CHILD sheet to work with
  • When I trigger only with new row added, I cannot follow up with changes made in an entry in MASTER
  • changes made in CHILD are not added to MASTER
  1. Report
  • I was successful in creating a report for mysite entries only but I cannot add columns to a report. Thus I cannot create a set of evaluation columns
  • I cannot feed the report into a new smartsheet/form that would be the base for my evaluation

If anyone has an idea how to do this, I'd be very happy. I am reaching a brick wall here.