Forum Discussion
Retrieving data from another closed Excel-file
I retrieve it by typing "=" and then choosing the cell in the source document with the data I want to retrieve.
Frallan13 wrote: I retrieve it by typing "=" and then choosing the cell in the source document with the data I want to retrieve.
The "=" part is given. Then we have to ask "How to you choose the specific cell in the source document?" Do you remember the column and row address?
I have a master spreadsheet that pulls in data from two source sheets. I usually have the source sheets open when doing so, so this may not be relevant. From time to time the numbers of rows in the source sheets change, sometimes up, sometimes down. I use COUNT to count the rows, and then INDIRECT to modify references accordingly. Something for you to try if it's at all relevant.
- Okay!
Well for exampel I retrieve A2, C2, E2, V2, W2 and AB2 from the source file and also the same columns for row 4, but not for row 3 which is irrelevant for the retrieving document.
Can you type the function you type with COUNT and INDIRECT that you use?
Then it might be easier for me to understand how to use them.I double checked my system and see that I mis-represented what I've been doing. It's so routine now that I don't pay any attention.
Actually, I bring into the master sheet the entirety of the two source sheets, using FILTER to accomplish that. This does require that the source sheets be open, as Sergei noted. For my purposes, that's not inconvenient at all. I open them without paying any direct attention to them,
Once they've been FILTERed in, I then use COUNT and INDIRECT to make sure the full newly imported table is correctly named in a Named Range.
I can then access whatever rows and columns I need to using XLOOKUP, VLOOKUP, INDEX, MATCH..whatever.
- Okey, thank you mathetes!
INDIRECT() requires that source file shall be opened, is that your case?
- It is okay that the source file is open. The problem is that when I add rows in the source file the retrieving file then collects the wrong row if not open while I add the row in the source file. We are multiple people working in the source file so thats why it will be difficult for everyone to always remember to open the retrieving file when adding rows.
