Feb 22 2021 06:42 AM
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?
Feb 27 2021 05:09 AM
Mar 04 2021 01:44 AM
Mar 04 2021 05:15 AM - edited Mar 04 2021 05:15 AM
@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.
Mar 04 2021 05:57 AM
Mar 04 2021 07:09 AM
INDIRECT() requires that source file shall be opened, is that your case?
Mar 04 2021 01:41 PM
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.
Mar 04 2021 10:16 PM