Selected Answer
I'm not entirely clear about what you want (and your worksheets are protected) but to my limited understanding of your problem it should be solvable with VLOOKUP. Please consider this formula as an example.
[Dashboard!B3] =VLOOKUP($A3,Scans!$A$2:$G$1000,2,FALSE)
I would urge you to assign a name to Scans!$A$2:$G$1000 and use that name in the formula. In that way, when the range changes you only modify the target associated with the name instead of a million formulas all over your workbook. Saves time and eliminates a source of errors.
Your big problem seems to be your understanding of times in Excel. A formula like =TEXT(C3-B3,"HH:MM:SS") displays the time as you want it but it is useless for calculations because it is text. In Excel times are numbers, and numbers can be used in calculations. Take your columns A and B on the Scans tab. Column B contains a number like 4359.583214 (format the cell as General to make it visible). This number is formatted as Date/Time with a cell format mask like m/d/yyyy hh:mm. You didn't do that, Exel did. You tried to change this format to h:mm AM/PM. That didn't work because of your sheet's protection. Users aren't allowed to Format cells. So you created column B. Actually, A and B have exactly the same value but formatted differently - or should have. But in this process you changed the value from number to text. Therefore column B is useless for calculation.
In a number like 4359.583214 the integer indicates the day. Time is indicated as a fraction of a full day to the right of the decimal point - down to a nano-second. If you ever need to calculate a break that occurs over midnight you can't do it unless your time values include the day. They shouldn't bother you and you can hide them from view by applying a suitable time format. However, if you wish to extract the time only from a date/time value use a formula like this (where the Date/Time value is in A3).
=MOD(A3,1)
