Need help with cross sheet reference to update a master tracker

Susan Pattison

I have two worksheets. A master tracker "Post Award Study Set up), and a sheet to receive new customer service requests that would pertain to either projects currently listed on the master tracker or would add a new line to it.

Workflow is such, my departmetn sees all new awards before any other Dept and we start a tracking line item in smartsheet. As the project progresses team members are assigned to different stages and start to ask for different types of deliverables. We do not know the team members in advance, they have to submit a form with a request for a delivery date and their name, email etc.

I'd like to cross reference the information we receive through form inputs as new staff join the project back to the master tracker, to update empty fields in the original entry as well as checkmark what deliverable has been requested.

See screen shot of Master Tracker (sheet to be ultimately updated ). See the yellow items that I'd like to be updated when we get new requests for service (from forms). In this example we logged the item to our worklist, but are waiting for the project to have a PM assigned and for them to submit a T/V request.

See screen shot of request form intake sheet, where all the form requests go when submitted (when the PM is assigned submits the request for service).

I Highlight the information to be added to the master tracker empty cells of the same name, IF the protocol number is ALREADY EXISTING on the master tracker, ELSE just add a new row to the master tracker with the request information. In this case, see Request tracker Protocol Number matches Master Tracker Protocol entry.

Thank-you!

Andrée Starå

Hi @Susan Pattison

I hope you're well and safe!

We'd use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet it will reflect on the destination sheet.

Can there be multiple entries in the request tracker with the same Protocol Number?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Susan Pattison

thank-you Andree,

Yes, it is possible the request tracker has multiple entries per protocol number. But they would not occur on the same date usually, but over time as the project progresses.

On which sheet do I set up the VLookup,or MatchIndex function? do I have to a "action" cell for the formula to sit in, it doesn't appear I can type a formula into cells that are of the checkbox type...

susan

Andrée Starå

@Susan Pattison

Happy to help!

You'd add the cross-sheet formula to the main sheet to collect the information from the other one. You should be able to add the formula to almost all columns.

Make sense?