Explanation

This formula relies on concatenation to assemble a valid location for the HYPERLINK function.

In cell D5, the link location argument is created like this:

"#"&B5&"!"&C5 // returns ""#Sheet1!A1""

which returns the string “#Sheet1!A1”. The formula then resolves to:

=HYPERLINK("#Sheet1!A1","Link")

Which returns a valid link.

The cell value in column C is entirely arbitrary and can be any cell you like. It could also be hardcoded into the formula as a string like this:

=HYPERLINK("#"&B5&"!A1","Link")

Note: The hash character (#) at the start of the sheet name is required. For more link syntax examples, see HYPERLINK .

Explanation

The core of this formula is the MODE function, which returns the most frequently occurring number in a range or array. The rest of the formula just constructs a filtered array for MODE to use in each row. The expanding range $D$4:D4 works to exclude numbers already output in $D$4:D4.

Working from the inside out:

  1. The MATCH checks all numbers in the named range “data” against existing numbers in the expanding range $D$4:D4
  2. ISNUMBER converts matched values to TRUE and non-matched values to FALSE
  3. 1-NUMBER reverses the array, and the math operation outputs ones and zeros
  4. IF uses the array output of #3 above to filter the original list of values, excluding numbers already in $D$4:D4
  5. The MODE function returns the most frequent number in the array output in step #4

In cell D5, no filtering occurs and the output of each step above looks like this:

{#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
{1;1;1;1;1;1;1;1;1;1;1;1}
{93;92;93;94;95;96;97;98;99;93;97;95}
93

In cell D6, with 93 already in D5, the output looks like this:

{2;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;#N/A}
{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
{0;1;0;1;1;1;1;1;1;0;1;1}
{FALSE;92;FALSE;94;95;96;97;98;99;FALSE;97;95}
95

Handling errors

The MODE function will return the #N/A error when there is no mode. As you copy the formula down into subsequent rows, you will likely run into the #N/A error. To trap this error and return an empty string ("") instead, you can use IFERROR like this:

=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")