读取工作表名公式_您的工作表名称正在破坏我的公式

读取工作表名公式

One of my Excel horror stories involves sheet names. I set up a client's workbook with pre-formatted data entry sheets, so sales managers could plan their annual product promotions. They would rename the sheets while working, to make it easier to navigate the completed workbook.

我的Excel恐怖故事之一涉及工作表名称。 我用预先格式化的数据输入表设置了客户的工作簿，以便销售经理可以计划其年度产品促销。 他们将在工作时重命名工作表，以便更轻松地浏览完成的工作簿。

On a hidden summary sheet, I added formulas to calculate the sheet names. Then, INDIRECT formulas pulled data from specific cells on each sheet, and other formulas created grand totals.

在隐藏的摘要工作表上，我添加了公式来计算工作表名称。 然后，INDIRECT公式从每张纸上的特定单元格提取数据，其他公式创建总计。

At the front of the workbook, the summary data was displayed in a monthly calendar, for sales managers to review. It was a work of art!

在工作簿的前面，摘要数据显示在每月日历中，供销售经理查看。 这是一件艺术品！

可怕的电话 (The Scary Phone Call)

Everything worked well in testing, so we distributed the files to all the sales managers, and they started filling in their data. The next day, the phone rang – some of the workbooks were "broken."

一切在测试中都运行良好，因此我们将文件分发给所有销售经理，然后他们开始填写数据。 第二天，电话响了–有些工作簿“坏了”。

Budget deadlines were looming, and the sales managers with broken files were in a panic. They sent me a couple of problem files, so I could figure out what was wrong.

预算截止日期迫在眉睫，文件损坏的销售经理感到恐慌。 他们给我发送了几个问题文件，所以我可以找出问题所在。

On the Summary sheet, some of the formulas were working correctly, but others showed #REF! errors. Comparing the good and bad sheets, I couldn't see any problems with the data that had been entered.

在“摘要”表上，某些公式运行正常，但其他公式显示为“ #REF！”。 错误。 比较好表和坏表，我看不到已输入数据的任何问题。

Finally, after checking a few of the problem sheets, I spotted a similarity. All of them included an apostrophe in the sheet name!

最后，在检查了一些问题单后，我发现了相似之处。 它们全都在工作表名称中包含撇号！

I removed the apostrophes, and the problem was solved. All the data showed up in the summary sheets, and the world was in harmony once again.

我删除了撇号，问题就解决了。 所有数据都显示在摘要表中，世界再次处于和谐状态。

图纸命名规则 (Sheet Naming Rules)

I hadn't anticipated that problem, since I never use apostrophes in sheet names. They're valid characters for a sheet name, but maybe they shouldn't be.

我没有预料到这个问题，因为我从未在工作表名称中使用撇号。 它们是工作表名称的有效字符，但也许不应该如此。

It's hard to find the sheet naming rules in Excel's help, but you may have seen an error message that lists them.

在Excel的帮助中很难找到工作表命名规则，但是您可能已经看到列出这些规则的错误消息。

1. The name can't be more than 31 characters, and you can't leave the sheet tab blank
   名称不能超过31个字符，并且您不能将工作表标签留空
2. Only a few characters are invalid:
   只有几个字符无效：

: / ? * [ ]

：/？ * []

工作表命名建议 (Sheet Naming Suggestions)

In addition to those rules, I have a couple of guidelines of my own.

除了这些规则之外，我还有一些自己的准则。

1. Use only letters, numbers and underscores in sheet names. Sometimes I have to use a space character, if a client requests specific sheet names, but I try to avoid it. For example, I'd use SalesData or Sales_Data, not Sales Data.

   工作表名称中只能使用字母，数字和下划线。 有时，如果客户要求使用特定的工作表名称，则必须使用空格字符，但我会尽量避免使用该字符。 例如，我将使用SalesData或Sales_Data ，而不是Sales Data 。

2. Use different names for sheets and named ranges, to avoid confusion.
   为工作表和命名范围使用不同的名称，以避免混淆。

您的工作表命名规则 (Your Sheet Naming Rules)

• What kind of names do you use for worksheets?
  您为工作表使用哪种名称？
• Any characters that you avoid or problems you've run into?
  您避免遇到任何字符或遇到问题吗？

翻译自: https://contexturesblog.com/archives/2009/10/26/your-sheet-names-are-killing-my-formulas/

读取工作表名公式