"Sheets" in Numbers do not have cells. A Sheet id the large 'canvas' on which you can place Tables and other objects, such as images, text containers or charts (graphs).
Tables hav cells, and each cell on a Table has an address consisting of its column letter and its row number.
The full address of a cell also includes the name of the Table containing the cell and the name of the Sheet on which that table in placed.
You wrote: " I am selecting ‘=‘ in the destination sheet/cell and no matter where I then choose on the source sheet/cell it is populating the formula box with the same text"
Here is a description of steps for doing this.
The example uses a Document with four sheets, named Sheet 1, Sheet 2, Sheet 3, and Sheet 4.
Each sheet contains a single table, named Table 1 on sheets 1 to 3, and named "Source" on Sheet 4.
You want to get the value in cell E5 of the table "Source" and place it in cell B5 of the table on Sheet 1
- Click on cell B5 in the destination table to select that cell.
- Press = to open the Formula Editor.

- Click the tab for Sheet 4 to move focus to that sheet
Note that the Formula Editor follows you to Sheet 4.
- On the table "Source, click cell E5.

- Then click the green checkmark on the Formula editor to confirm the formula and close the editor and return the focus to Table 1 on Sheet 1.

Note that the cell reference included the nam of the Table, but not of the Sheet. As the document contains only a single table named "Source", Numbers will drop the sheet name as it is not needed to locate the cell.
If this process does not give the desired result, we'll need to take a closer lo at how 'your source table differs from other source tables in your document.
Regards,
Barry.