Connecting HR info - diff tabs, IF criteria, etc.

If your position numbers always start with that format (3 numbers then hyphen then a three-digit code for the sheet), then put this in D2 and drag down:

Formula:

=INDIRECT("'"&MID(A2,5,3)&"'!A2")

The first A2 refers to the A2 on Sheet 1, the second to the relevant tab (just in case there's any confusion).

Edit: If the position number format might change, there'll need to be a 'Find' function embedded in the 'Mid' function - not too hard, but it would make the formula look a lot more complicated and there's no point if it's not needed.

ps Welcome to the forum.