Excel超链接到下一个空单元格

Last week, someone asked me how to make an Excel hyperlink to next empty cell. See how to set that up, in a couple of different situations.

上周，有人问我如何使Excel链接到下一个空单元格。 了解如何在几种不同的情况下进行设置。

超链接到下一个空单元格 (Hyperlink to Next Empty Cell)

The request was for a cell that said "Add Data", with a link to the next empty cell in a data entry list.

该请求是针对一个说“添加数据”的单元格，并带有指向数据输入列表中下一个空单元格的链接。

It wasn't clear is the list was just on the worksheet, or in a named table, so I made 2 examples.

目前尚不清楚该列表是仅在工作表上还是在命名表中，因此我举了两个例子。

下一个空单元格–命名表 (Next Empty Cell – Named Table)

In the first example, the list is formatted as a named Excel table, with the heading in cell D3.

在第一个示例中，列表的格式设置为命名Excel表 ，标题位于单元格D3中。

If you point to the hyperlink in cell B1, it shows the address of the next empty cell below the table – cell A7.

如果指向单元格B1中的超链接，它将显示表格下方的下一个空单元格的地址-单元格A7。

HYPERLINK函数–命名表 (HYPERLINK Function – Named Table)

In cell B1, there's a formula with the HYPERLINK function. The HYPERLINK function has 2 arguments:

在单元格B1中，有一个带有HYPERLINK函数的公式。 HYPERLINK函数具有2个参数：

1. link_location – where the link should take us
   link_location –链接应该带我们到哪里
2. friendly_name – text for the hyperlink (optional)
   friendly_name –超链接的文本(可选)

友好名称 (Friendly Name)

Let's look at the easy part of that formula first – the friendly name.

首先让我们看一下该公式的简单部分-友好名称。

In this example, cell B1 should say, "Add Entry", so that will be in the 2nd argument.

在此示例中，单元格B1应该说“添加条目”，这样它将在第二个参数中。

=HYPERLINK(link_location, "Add Entry")

= HYPERLINK(link_location， “添加条目” )

NOTE: If you omit the friendly name argument, the cell shows the link location.

注意 ：如果您省略友好名称参数，则单元格将显示链接位置。

链接位置 (Link Location)

When you're linking within an Excel workbook, the link has to start with a number sign – #.

在Excel工作簿中进行链接时，链接必须以数​​字符号- ＃开头。

The next empty cell will be somewhere in column A, below the last row in the named table, tblDays.

下一个空单元格将在A列中命名表的最后一行tblDays下面的某处。

Here's the start of the link location argument, with those 2 characters:

这是链接位置参数的开头，带有这两个字符：

=HYPERLINK("#A"

= HYPERLINK(“＃A”

空单元格行号 (Empty Cell Row Number)

Next, the formula has to calculate the row number for the next empty cell.  That row is the SUM of these 3 numbers:

接下来，公式必须计算下一个空单元格的行号。 该行是以下三个数字的总和：

• • =ROW(tblDays[[#Headers],[Day]])
    = ROW(tblDays [[＃Headers]，[Day]])

  The table's starting row

  表格的起始行

• • =COUNTA(tblDays[Day])
    = COUNTA(tblDays [Day])

  The number of data rows in the table

  表中的数据行数

• Plus one, to get to the empty cell below the table

  加一 ，进入表格下方的空白单元格

Here's the formula in cell B1:

这是单元格B1中的公式：

=HYPERLINK("#A" & SUM(COUNTA(tblDays3[Day]), ROW(tblDays3[[#Headers],[Day]]), 1), "Add Entry")

= HYPERLINK(“＃A”＆ SUM(COUNTA(tblDays3 [Day])，ROW(tblDays3 [[＃Headers]，[Day]])，1) ，“添加条目”)

测试超链接 (Test the Hyperlink)

To test the hyperlink, try these steps:

要测试超链接，请尝试以下步骤：

• Click the hyperlink to go to cell A7, and type a day name there.
  单击超链接转到单元格A7，然后在其中键入日期名称。
• Then, point to the hyperlink in cell B1 again.
  然后，再次指向单元格B1中的超链接。

The screen tip now shows A8 as the address that it will go to, as the next empty cell.

屏幕提示现在显示A8作为它将到达的地址，作为下一个空单元格。

HYPERLINK功能–工作表 (HYPERLINK Function – Worksheet)

When the list is on a worksheet, instead of a named table, the empty row calculation is different. The rest of the formula is the same.

当列表在工作表上而不是命名表上时，空行计算是不同的。 其余公式相同。

That row is the SUM of these 2 numbers:

该行是这两个数字的总和：

• • =ROW(A3)
    = ROW(A3)

  The list starting row

  列表开始行

• • =COUNTA(A:A)
    = COUNTA(A：A)

  The number of cells with data in column A

  A列中有数据的单元格数

Here's the formula in cell B1:

这是单元格B1中的公式：

=HYPERLINK("#A" & SUM(COUNTA(A:A), ROW(A3), "Add Entry"))

= HYPERLINK(“＃A”＆ SUM(COUNTA(A：A)，ROW(A3) ，“添加条目”))

更多HYPERLINK示例 (More HYPERLINK Examples)

To see more ways to use the HYPERLINK function, watch this short video.

要查看更多使用HYPERLINK功能的方法，请观看此简短视频。

For the written steps and sample file, go to the Hyperlinks page on my Contextures site.

有关书面步骤和示例文件，请转到我的Contextures网站上的“超链接”页面 。

演示地址

获取样本文件 (Get the Sample File)

There are several sample files on my Excel Hyperlinks page, and the Empty Cell hyperlink example is in the first workbook – General Examples.

我的Excel超链接页面上有几个示例文件，第一个工作簿中的“空单元格”超链接示例–常规示例。