动态参考Excel工作表 [英] Reference Excel worksheets dynamically

问题描述

我有一个应该很简单的问题，但是我没有解决这个问题.我为一家商店印刷了价目表，今年他们将零件号分成了5个工作表，而不是一个.

I have a problem that should be so simple, but I'm not getting my head around it. I do printed pricelists for a store and this year they've split up the part numbers into 5 worksheets instead of one.

当用户想要打印价格标签时，她在C10中输入=，然后单击价格表"工作表并导航至所需的部件号.

When a user wants to print out a pricetag, she enters the = into C10, clicks the Worksheet "Pricelist" and navigates to the part number she needs.

C10的最终公式为: =价目表！B40

The resulting formula for C10 is: =Pricelist!B40

E10必须包含有关零件号的更多信息，因此E10公式为: = VLOOKUP(C10，Pricelist！B:N，2，FALSE)

E10 must contain more info about the part number so E10 formula is: =VLOOKUP(C10,Pricelist!B:N,2,FALSE)

但是，现在她有了新的工作表，她可以选择工作表"New_Items"，在这种情况下，C10的最终公式为: = New_Items！B40

However, now with the new worksheets she might select Worksheet "New_Items" in which case the resulting formula for C10 is: =New_Items!B40

如何编写E10的公式，以便它引用与C10相同的工作表.

How can I write the formula for E10 so that it references the same worksheet that C10 does.

我需要E10 = VLOOKUP(C10，价目表！B:N，2，FALSE)

I need E10 =VLOOKUP(C10,Pricelist!B:N,2,FALSE)

自动成为* = VLOOKUP(C10， New_Items ！B:N，2，FALSE)*

to automatically become *=VLOOKUP(C10,New_Items!B:N,2,FALSE)*

那有意义吗?这样可以使Excel根据另一个单元格的引用修改工作表引用吗?

That make sense? Is that doable to have Excel modify a worksheet reference based on another cell's reference?

TIA！

推荐答案

是的，您可以使用INDIRECT()进行此操作，因为您需要以某种方式确定工作表的名称，所以这可能需要一些工作.

Yes you can do this using INDIRECT() It might take a bit of work since you need to work out the name of the sheet somehow.

=VLOOKUP(C10, INDIRECT( Concatenate(cell-with-sheet-name, "!B:N"),TRUE ) ,2,FALSE)

这篇关于动态参考Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！