0

I'm trying to get some data from the excel files in a folder, and I already know how to get some data from an external files.

How can I you get the data based on the file's name without typing it manually? Can I just reference the cell next to it as the file name?

Note that all of the town files have the same exact structure with different data.

Example files: Example Files

Screenshot.

1 Answer 1

0

Unfortunately, it won't work directly as you would like it too.

You can use INDERECT formula

=INDIRECT("["&A1&".xlsx]Sheet1!$B$3")

where A1 is your reference name.

Unfortunately, this method is very limited for external books situations, because it needs all the books to be open to work. But in some cases, it can suit the need, may be yours is the one.

All the other ways I know of are much more complicated using VBA or probably Power Query.

2
  • Alright, Thanks. But the Indirect method doesn't seem to fit my needs. Do you have any course/YT videos/documents recommendation to learn these complicated method? I will probably need to learn Advance excel anyway. Feb 17, 2023 at 0:34
  • I would recommend going with VBA then. There is nothing fancy about it, just logic. The code is quite user friendly and very forgiving. And you don't need to learn everything fist. Just learn as much as you need for your task. There are some nuances involved with xlsm files. I personally try to avoid VBA as much as possible, but often it is the best choice anyway. There is plenty of info and examples on the Internet. The first search result from google seems to be putting you into the right direction. Good luck. google.com.ua/… Feb 17, 2023 at 14:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .