Reference another sheet cells dynamically?

[Novak]

Ok I have a problem and I am new to speadsheets and “advanced” features I have no clue. I know how to reference cells from another sheet, but what I can not figure out is how to reference something where data is changed each week so row numbers are not the same?

What I do is that I download each week a new file with financial data which changes in rows since new data/rows is added each week. I import this file (insert sheet from file) and filename is always the same.

Now to the main issue.

Since new data is added, new rows are created and reference therefor is wrong?test.ods
What is the best way to do in this scenario?

I have attached the file I am working with and in the “euro” sheet I have created how I would like the output to look like, however next week, the rows are different since a new row has been added to Euro. How to do this?
Should I use some kind of function like index or vlookup? If so - how?

[LeroyG]

@Novak,

Not clear how do you use the separated data.

If you only need to see it: select all cells (A1:DW1108), in menu Data select AutoFilter, select the new arrow in A1, uncheck the box near All, and check the box near EURO FX - CHICAGO MERCANTILE EXCHANGE.

You can also hide some unneeded columns.

If you want to add more information, you can edit your question, or comment an answer. Do not use Answer to comment.

Mark the circle to the left of the answer that solves your question.

[keme]

A filter is often the better solution. Do not read further until you have seen the other answer about filters.

If you really need a dynamically generated range reference, there are two functions commonly used for this, namely INDIRECT() and OFFSET().

To determine table size, the COUNT() or COUNTA() function can be used if you know that there are no empty cells.

It sems that most users prefer INDIRECT(), where you build the address as a text string expression. With this, it is often a good idea to use the string from the ADDRESS() function as a starting point.

My preference is OFFSET(), where you start with a cell address and shift and extend from there.

Read the help for those functions. Post a comment with more detail if you need further assistance.