2 tracker sheets, merged into one resource sheet?

santiago.rendon · 03/19/25

Hello,

I have two sheets, one is a fabrication tracker that tracks different jobs in our machine shop, and we have a design tracker that tracks current designs being worked on.

I would like to combine both trackers into one resource tracking sheet that shows [Name] [Job #/ Design #"] [Role] [% Occupied].

Image below is from our design tracker, i would like to pull out all 4 columns into the new sheet.

Image below is from our fab tracker, where the only info i want to pull out of this is the machinist name and the job #

Image below is the resource tracker

Basically, I would like to pull all the names automatically into this sheet, alongside the job #'s/DRR #'s, and whatever the role they are doing for that job #.

Can I do something where someone manually inputs the job # and the rest gets filled out?

Adam Murphy · 03/19/25

You could use an INDEX/MATCH or INDEX/COLLECT formula to pull in corresponding values based on Job #.

santiago.rendon · 03/19/25

@Adam Murphy Would that pull in relevant role information into the one column that has Designer/Drafter/Reviewer/Machinist?

And would that also merge the different job numbers into the one column as well or would it be better to have two separate columns for that?

Adam Murphy · 03/21/25

You need to have a unique value in each sheet that you can use to reference in your resource tracker. I assume Job # and DRR # are not the same, so what value is linking the records?

For example, if in your resource sheet you had the DRR# in a column you could then add a formula to an individual Reviewer column that collected the name of the reviewer for that DRR# from the first sheet (fabrication tracker?). Something like =INDEX(COLLECT({Reviewer}, {DRR#}, [DRR#]@row), 1)

2 tracker sheets, merged into one resource sheet?

Answers

Help Article Resources

Categories