2 tracker sheets, merged into one resource sheet?

santiago.rendon

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

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

santiago.rendon

@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

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)