pick data from a chosen tab in excel

问题:

I have the following problem. I have a file with many tabs. the name of each tab is a country and in the tab there are various data about the country. I would like to create an interface tab, where the user can put the country name in a cell (eg "France" cell A1), the data item in another cell (eg "Births") and the interface will give the time series of the data item ("Births" in my example). Is there a way to do it using Excel and not VBA? I can do it with VBA, but I need an Excel-only way to do it. Thanks in advance.

回答1:

With France in A1 and Births in A2, you would need a named range with worksheet scope called Births on the France worksheet. Then you can use the INDIRECT function to piece the address together.

=indirect("'" & a1 & "'!" & a2)

But you probably do not want to make all of those named ranges for every worksheet. You will have to provide a better description of your data layout on each worksheet for more specific assistance.