尝试在多个工作表中查找特定行/范围中的最后一个非空单元格 [英] Trying to find the last non-empty cell in a specific row/range over multiple sheets
问题描述
我正在Excel 2013中进行排序工作簿。工作簿包含一个计算一年的每个月的表格，一份列表和一张表。 列表列表中列出了名称和月份列表。我已经包含了一个示例工作簿的链接，以演示如何设置表格以及我使用的基本功能，名称，列表等。
我的示例工作簿：
https://app.box.com/s/mgvums1vmnmnec7e7d0p4vi5daoyo5nv
当查找特定行/范围中的最后一个非空单元格时，当我在每个特定的月份表上使用以下LOOKUP时，我收到正确的结果：（我发现一些在几个网站上挖掘）= LOOKUP（2,1 /（C5：AG5 - ），C $ 4：AG $ 4）
- 对于2月份，C5：AG5范围内的最后一个条目是在AA5，这是第25。
当我从任何非月份表中使用以下查询时，我也收到正确的结果：= LOOKUP（2,1 /（Feb！C5：AG5& ），Feb！C $ 4：AG $ 4）
据说，我遇到的两个具体问题如下：
- 当使用名称和月份信息选择时，我无法弄清楚最后一个非空单元格（这是最后一个标记出席日）在组合框/表单控件。简单地说，在我的样本工作簿中，我想知道约翰尼出席的最后一天。
我试过开始简单，只需使用我选择的月。我尝试了以下几个不同的代码：
= LOOKUP（2,1 /（月&C5：AG5，），月&！C $ 4：AG $ 4）
最接近的是上面的Lookup，最终变成你在下面看到的（当在Calculation步骤中查看时）。它当然会返回#VALUE！错误。
= LOOKUP（2,1 /Feb！C5：AG5，，月&！C $ 4：AG $ 4）
也试过这个数组公式，它给了我一个结果4.哪个不是正确的结果。而且，我不知道结果实际来自哪里。
= MAX（（INDIRECT（F4&！&C5：AG5）<）*（ROW（INDIRECT（F4&&C $ 4：AG $ 4） ）））
- 我一直无法弄清楚如何找出最后一个非空单元格使用从组合框/表单控件中选择的名称的月份表。基本上，我想知道约翰尼出席的那一年的最后一天。在这份样本工作簿中，将于4月11日发布。我知道公式只会返回11。我相信我可以使用结果表格名称来格式化结果。 （在这个示例案例中，Apr）
我相当确定我将需要使用VLOOKUP。我似乎无法打破我想要的一般概念与正确的方式之间的障碍。
任何帮助都不会感谢。我现在感觉很迷茫。如果你需要更多的信息，请让我知道。
谢谢，
Kurewe
关键是使用与您已经完全相同的 LOOKUP 公式的 INDIRECT 使用 MATCH 公式确定所选名称的行号。
放置以下公式：
- G3：
= MATCH（F3，NameList，0）+4（找到名称列表中所选名称的项目号） - H2：
= F4&！C4：AG4（构造所选月份的日期行） - H3：
= F4&！C&G3&：AG& G3
- E9：
= INDEX（MonthList，ROW（） - ROW（E $ 8） code> 复制到E20 （使用MonthList找到此行的名称） - G9：
= E9& ;！C4：AG4复制到G20 （con将E9中的月份的日期行的地址结构化为 - H9：
= E9&！C& G $ 3&：AG& ; G $ 3复制到H20 （构造用于所选名称和E9中的月份的地址） - F9：
= IFERROR（LOOKUP（2,1 /（INDIRECT（H9）<>），INDIRECT（G9）），（n / a））strong>复制到F20 （使用G9& G中的地址，找到最后一天的X G4：= LOOKUP（2,1 / ISNUMBER（F9：F20），ROW（F9：F20）-ROW（F8））（在其中找到第一个单元格的索引（n / a）） - F5：
= LOOKUP（2,1 /（ INDIRECT（H3）），INDIRECT（H2））（在H2和H3的地址中找到最后一天的X） - F6：
= INDEX（MonthList，G4）& - & INDEX（F9：F20，G4）（构造最后的日期一年内出席日）
I looked at a couple suggestions here and didn't find something that specifically worked for me. I would prefer not to use an Array Formula if possible. However, if that's the only way, I'll try to work with it.
I am working on an attendance workbook of sorts in Excel 2013. The workbook contains a Calculations sheet, a Lists sheet and a sheet for each month of the year. The Lists sheet has a list for names and a list for the months. I have included a link to a sample workbook to demonstrate how the sheets are setup and the basic functions, names, lists, etc that I am using.
My sample workbook: https://app.box.com/s/mgvums1vmnmnec7e7d0p4vi5daoyo5nv
When looking for the last non-empty cell in a specific row/range, I receive the correct result when I use the following LOOKUP on each specific Month sheet: (Which I found after some digging on a few sites) =LOOKUP(2,1/(C5:AG5<>""),C$4:AG$4) - For the Month of Feb, the last entry in the range C5:AG5 is in AA5, which is the 25th.
I also receive the correct result when I use the following Lookup from any non-Month sheet: =LOOKUP(2,1/(Feb!C5:AG5<>""),Feb!C$4:AG$4)
That being said, the two specific issues I am running into, are as follows:
- I have been unable to work out how to come up with the last non-empty cell (which is the last marked day of attendance) when using Name and Month information selected in the Combo Box/Form Controls. Simply said, in my sample workbook, I want to know the last day in Feb that Johnny was in attendance.
I've tried starting simple, just using the "Month" I selected. I tried a few different itterations of the following: =LOOKUP(2,1/(Month&"!C5:AG5<>"""),Month&"!C$4:AG$4") The closest I've gotten was the above Lookup, which ends up turning into what you see below (when viewed in Calculation steps). It of course returns a #VALUE! error. =LOOKUP(2,1/"Feb!C5:AG5<>""",Month&"!C$4:AG$4")
I also tried this Array Formula and it gave me a result of 4. Which, isn't the proper result. And, I don't know where the result actually came from. =MAX((INDIRECT(F4&"!"&"C5:AG5")<>"")*(ROW(INDIRECT(F4&"!"&"C$4:AG$4"))))
- I have been unable to work out how to come up with the last non-empty cell across all the Month sheets when using the selected Name from the Combo Box/Form Control. Basically, I want to know the last day of the year that Johnny was in attendance. In this sample workbook, that would fall on April 11th. I understand that the formula would just return "11". I believe I can format the result after the fact, using the resulting sheet name. (In this sample case, Apr)
I am fairly certain I will need to use a VLOOKUP. I just can't seem to break the barrier between the general concepts of what I want and the proper way of formulating it.
Any help would be appreciated. I'm feeling pretty lost at the moment. If you need more info from me, please let me know.
Thanks, Kurewe
The key is to use INDIRECT with the exact same LOOKUP formula you already had and use the MATCH formula to determine the row number for the selected name.
Place the following formulas:
- G3:
=MATCH(F3,NameList,0)+4(find the item number of the selected name within NameList) - H2:
=F4&"!C4:AG4"(construct the address for the date row for the selected month) - H3:
=F4&"!C"&G3&":AG"&G3(construct the address to use for the selected name and month) - E9:
=INDEX(MonthList,ROW()-ROW(E$8))copy down to E20 (find the name of the month for this row using MonthList) - G9:
=E9&"!C4:AG4"copy down to G20 (construct the address for the date row for the month in E9) - H9:
=E9&"!C"&G$3&":AG"&G$3copy down to H20 (construct the address to use for the selected name and the month in E9) - F9:
=IFERROR(LOOKUP(2,1/(INDIRECT(H9)<>""),INDIRECT(G9)),"(n/a)")copy down to F20 (find the last day with an "X" in it using the addresses in G9 & H9) - G4:
=LOOKUP(2,1/ISNUMBER(F9:F20),ROW(F9:F20)-ROW(F8))(find the index of the first cell with "(n/a)" in it) - F5:
=LOOKUP(2,1/(INDIRECT(H3)<>""),INDIRECT(H2))(find the last day with an "X" in it for the addresses in H2 & H3) - F6:
=INDEX(MonthList,G4)&"-"&INDEX(F9:F20,G4)(construct the date of the last day of attendance within the year)
这篇关于尝试在多个工作表中查找特定行/范围中的最后一个非空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
