Retrieving data from another closed Excel-file

%3CLINGO-SUB%20id%3D%22lingo-sub-2155815%22%20slang%3D%22en-US%22%3ERetrieving%20data%20from%20another%20closed%20Excel-file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2155815%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3CBR%20%2F%3EI%20have%20two%20Excel-files%20saved%20on%20Teams%20in%20seperat%20folders.%26nbsp%3B%3CBR%20%2F%3EOne%20of%20the%20Excel-files%20is%20retrieving%20data%20from%20the%20other%20(source%20document).%26nbsp%3B%3CBR%20%2F%3EI%20have%20noticed%20that%20when%20I%20add%20a%20row%20in%20the%20source%20document%20while%20the%20retrieving%20document%20is%20closed%2C%20the%20retrieving%20document%20is%20then%20retrieving%20the%20wrong%20row%20when%20opened.%26nbsp%3B%3CBR%20%2F%3EThe%20retrieving%20document%20understands%20that%20a%20row%20has%20been%20added%20if%20it%20is%20open%20while%20the%20change%20is%20made%20to%20the%20source%20document.However%2C%20it%20is%20not%20always%20that%20I%20remember%20to%20open%20the%20retrieving%20document%20while%20making%20changes.%26nbsp%3B%3CBR%20%2F%3EIs%20there%20a%20way%20to%20work%20around%20this%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2155815%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2170992%22%20slang%3D%22en-US%22%3ERe%3A%20Retrieving%20data%20from%20another%20closed%20Excel-file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2170992%22%20slang%3D%22en-US%22%3EHi..%3CBR%20%2F%3Ehow%20are%20you%20retrieving%20the%20data%20%3F%3CBR%20%2F%3ERegards%2C%20Faraz%20Shaikh%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2184274%22%20slang%3D%22en-US%22%3ERe%3A%20Retrieving%20data%20from%20another%20closed%20Excel-file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2184274%22%20slang%3D%22en-US%22%3EHi%20Faraz%2C%3CBR%20%2F%3EI%20retrieve%20it%20by%20typing%20%22%3D%22%20and%20then%20choosing%20the%20cell%20in%20the%20source%20document%20with%20the%20data%20I%20want%20to%20retrieve.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2184727%22%20slang%3D%22en-US%22%3ERe%3A%20Retrieving%20data%20from%20another%20closed%20Excel-file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2184727%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F974755%22%20target%3D%22_blank%22%3E%40Frallan13%3C%2FA%3E%26nbsp%3B%20wrote%3A%3CEM%3E%20I%20retrieve%20it%20by%20typing%20%22%3D%22%20and%20then%20choosing%20the%20cell%20in%20the%20source%20document%20with%20the%20data%20I%20want%20to%20retrieve.%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%22%3D%22%20part%20is%20given.%20Then%20we%20have%20to%20ask%20%22How%20to%20you%20choose%20the%20specific%20cell%20in%20the%20source%20document%3F%22%20Do%20you%20remember%20the%20column%20and%20row%20address%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20master%20spreadsheet%20that%20pulls%20in%20data%20from%20two%20source%20sheets.%20I%20usually%20have%20the%20source%20sheets%20open%20when%20doing%20so%2C%20so%20this%20may%20not%20be%20relevant.%20From%20time%20to%20time%20the%20numbers%20of%20rows%20in%20the%20source%20sheets%20change%2C%20sometimes%20up%2C%20sometimes%20down.%20I%20use%20COUNT%20to%20count%20the%20rows%2C%20and%20then%20INDIRECT%20to%20modify%20references%20accordingly.%20Something%20for%20you%20to%20try%20if%20it's%20at%20all%20relevant.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi!
I have two Excel-files saved on Teams in seperat folders. 
One of the Excel-files is retrieving data from the other (source document). 
I have noticed that when I add a row in the source document while the retrieving document is closed, the retrieving document is then retrieving the wrong row when opened. 
The retrieving document understands that a row has been added if it is open while the change is made to the source document.However, it is not always that I remember to open the retrieving document while making changes. 
Is there a way to work around this?

8 Replies
Hi..
how are you retrieving the data ?
Regards, Faraz Shaikh
Hi Faraz,
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.

@Frallan13 

INDIRECT() requires that source file shall be opened, is that your case?

@Frallan13 

 

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!
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.