In Numbers, how can I get a cell value from another sheet with a formula?

ADDRESS has an optional parameter specifying the table name for the reference which can span across sheets by specifying the sheet name before the table name.

ADDRESS(row, column, addr-type, addr-style, table)

table: An optional value specifying the name of the table. table is a string value. If the table is on another sheet, you must also include the name of the sheet. If omitted, table is assumed to be the current table on the current sheet (that is, the table where the ADDRESS function resides).

=ADDRESS(3, 3,,,"Sheet 2::Table 1") creates the address Sheet 2::Table 1::$C$3.

ADDRESS returns a cell address string which can be used to look up the value located in the referenced cell using INDIRECT(addr-string, addr-style).