How to link data from two spreadsheets imported to sql
The only connection I found is that the Reference column in Sheet 2
  has numbers that match the ParentID on sheet 1 but with a suffix
  appended.


You need to match float value 10125 (column ParentID) on Sheet1 with NVarchar(255) value 10125_Motsomane (column Reference) from Sheet2.

If data types would match you would simply do a direct INNER JOIN:

SELECT
    S1.ParentID,
    S2.Reference,
    AnyOtherColumnYouNeed
FROM
    Sheet1 AS S1 
    INNER JOIN Sheet2 AS S2 ON S1.ParentID = S2.Reference


But to extract the number before the underscore you can use CHARINDEX to find the position of the underscore and then LEFT to get the value up to that position:

DECLARE @ExampleValue NVARCHAR(255) = N'10125_Motsomane'

SELECT
    Original = @ExampleValue,
    Trimmed = LEFT(
        @ExampleValue,
        CHARINDEX(N'_', @ExampleValue) - 1)


Result:

Original            Trimmed
10125_Motsomane     10125


Applying this on the join (will do an implicit conversion from NVARCHAR to FLOAT):

SELECT
    S1.ParentID,
    S2.Reference,
    AnyOtherColumnYouNeed
FROM
    Sheet1 AS S1 
    INNER JOIN Sheet2 AS S2 ON S1.ParentID = LEFT(S2.Reference, CHARINDEX(N'_', S2.Reference) - 1)


Keep in mind that the LEFT function will fail if there is no underscore on a value for Reference, since it will return -1 as position.