How to fetch a cell from a different excel file, which is closed & in a different folder

Hello everyone!

This forum is great and I used it several times already! I hope you guys can help me with my problem. I will try and describe it as clearly as I can and what I need to achieve:

I have ONE excel file (lets call it MAIN.XLS). I use this excel file to create and list products for an ecommerce website. It’s a very simple sheet with some vlookup function. Nothing special really. You can consider it as a template of some sort.

Now, we have 2,000 different folders and inside each folder we have one .CSV file. Inside each .CSV file we have 6 cells with some numbers/data (different data for each file). The filename of the .CSV is the same on all folders (Data.csv) and the layout is also the same on all all files.

Before I will continue, its important to mention that I will copy the main template file (MAIN.XLS) to each for the folders in order for what I will write below will work.

Ok, now what I wanted to achieve was something simple, or so I thought – take the main file MAIN.XLS and on cell A7, I wanted to take data from cell A2 from the .CSV file (=[Data.csv]Data!$A$2). The problem its not working on other folders. My guess was because the link is broken due to location of file.

I have searched around and found that there is a formula for a file location - =LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1) – so if I insert this formula it should give me the same folder as the .csv file (since I put both files on same folder). However it did not worked.

My English is not very good so I hope I explained it fine. If not clear please tell me and I will do my best to try and explain it again.

Thanks again for all the help!

Mod Edit: OP has redefined the issue with a new attachment in post #25.

Welcome to the forum!

Please fill in your Office version and location (UK, USA, etc.) in your user profile as these are important for helpers to know when tailoring suggestions to your locale and Office version.

Advice to work in one folder, instead of multiple folders.

Attach your main worksheet as well as two three supported files which is in different folders. With mentioning enter manually expected result in respective cells.

Thank you very much for the reply. My english is not very good. can you please try and explain it again maybe even with an example how I can do it on my excel?

Thanks again!! Still have hope to solve this problem!

You are being asked to provide your workbooks. You can attach them here. Make sure you desensitise them first.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

There is no data in the sample files - we need to be able to see what you are trying to do here.

OK - thanks for the clarification.

Be patient - the volunteers here will look at the problem when they are ready. Your English is not so bad!

Kindly attach file with your formula's. Input some more data.

Have you considered Power Query for this?

I am not really clear what the purpose is: if you could explain the ultimate goal, it might make it easier to help. Why do you need to do this?

I’m not sure what Power Query is or how to use it.

Well, the purpose of this is this – the MAIN excel file is acting a main template to list products to a website. I just fill the text and numbers to this excel sheet and the product listing is complete. for example, product title, category, price, etc. just simple attributes.

Now, each of the folders with DATA files contain the text/data needed to list each product. Instead of having to open each time the data.xlsx files and then copy the data into the main excel template, then save it again as different name and put into different folder, I was thinking just create a simple formula to one file (the main excel file) that will fetch the data needed automatically from the folder we copy this file to. I was thinking it’s a very simple solution and also would be very easy to make but seems pretty complicated?

Thanks!

So use Power Query (Get & Transform on the Data Ribbon).

In the source document, establish the master document as the data source (Data > Get Data > From File ...).

I tired to search where it is as I was not able to locate it. After some google search, this feature is not available for Mac users.

Please update your forum profile to include Mac in your Office version. It will prevent people from wasting their own time and yours. Thanks.

Thanks.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

No problem. Noted.

Also, after some testing I think I maybe close to find out the solution but I don’t have the know-how to properly do it;

I put both files on same folder. For the MAIN template file I insert on B1 this formula: =LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)&"DATA.xlsx", which gives me the location and filename for the data excel file. On A2 (which is the cell I want to show the info I fetch from the different workbook, DATA.xlsx) I put =TRIM([B1]DATA!A2), of course this doesn’t work and ask me to choose the file, then it works, but maybe something like this should work. I simply don’t have the expertise to correct this formula but all missing pieces are here.

Thanks!

Threads merged and moved to the VBA section. I have also amended the original title.