I am receiving an error when attempting to use a formula without saving the record in the 'In Progress' table (B). However, once I save the record in the 'In Progress' table (B), the same formula works fine.

Don't have a lot of time either, but as everyone says - the record ID does not exist until saved. Luckily, during an add record, when using a custom button on that sheet, you can use the %%rid%% and SaveBeforeNavigating commands to capture the newly saved record ID. Basic button code below, you would just need to sub in your information. This would use a formula rich text field.

var text RID = If([Record ID#]>0, ToText([Record ID#]), "%%rid%%");

var text EditRecord = URLRoot() & "db/" & dbid() & "?act=API_EditRecord"
& "&apptoken= xxxxxx" // if you have the need for Application Tokens enabled.
& "&rid=" & ToText($RID)
& "&_fid_99=now"; // sets this field to current date time but you can set any fields you need as typical 

var text DisplayRecordButMissingRID = URLRoot() & "db/" & dbid() & "?a=dr&rid=";

var text URL =
$EditRecord
& "&rdr=" & URLEncode($DisplayRecordButMissingRID) & $RID; // the %%RID%% does not like being URLEncoded.

//below makes the button. Save before navigating and data-replaceRID=true are the important bits

"<a class='SaveBeforeNavigating' data-replaceRid=true style=\"text-decoration:none; background: #1ba802; border-radius: 5px; color: #ffffff; display: inline-block; padding: 2px 2px 2px 2px; width:200px; text-align: center; text-shadow: none; border: 2px solid #1ba802; font-size: 18px \"href='"
& $URL
& "'>Button Words Here</a>"

The record ID (or rid) is not assigned until the record is saved for the first time.