OK, first of all, are you really linking two databases, or just two tables in a single database?  I'm assuming, from the description, that you are just working with tables.

Second, Crystal will evaluate the links long before it evaluates any formulas.  So, you can't use formulas to modify how Crystal pulls data from the database (either with linking or in the Select Expert).

The simplest solution is to create a query in Access.  You can then use this query as the data source for your report.

If you don't have that option, you will need to use the Add Command feature in the Database Expert to use SQL to define the join.  I would need to know what your @ShamrockNumber formula looks like to give you a hint on how to structure the SQL.

Sure you can.  Look into creating linked tables in Access (which is, oddly enough, totally different from linking tables in Crystal).  These are aliases to tables in other locations (including Excel spreadsheets) that can be used just like regular tables.  I think that you'll find that maintaining them in Access is a bit easier than working multiple datasources in Crystal.

So, your join is going to be a bit tricky, because it's got an IF in there.  But, this should work (note that you will not be able to view it in the Query Builder in Access, and will only be able to see the SQL):

SELECT TBL_HISTORY.*, Sheet1.*
FROM TBL_HISTORY
JOIN Sheet1
ON Sheet1.[S Number] = 
    IIF(LEN(TBL_HISTORY.PLANT_ITEM_CODE)=12,
        RIGHT(TBL_HISTORY.PLANT_ITEM_CODE,4),
        RIGHT(TBL_HISTORY.PLANT_ITEM_CODE,5))

Obviously, you will need to add a WHERE clause, and you will probably want to actually specify the field in the SELECT clause.  But, that should get you started.