Hi all
Question: Will OFFSET slow down my spread sheet in this scenario?
The task is to create a table with data such as: date on the left, followed by columns with data corresponding to different metrics about a product I am searching. This info is being taken from an external excel file. One table is around 15 rows and 3-6 columns. I would have around 150 of these tables in my file with a grpah next to it.
This is a "terminal" file with charts and tables, it's not a model and no calculations take place in this file. It only has to get data from existing sources.
It's not easy to explain it here, but using INDEX MATCH ONLY for this would take me ages. I have to make a file with hundreds of graphs and each graph has one of these tables with data next to it. I would have to copy the index match formula and then manually change the row number where the data comes from (the external excel file) in-formula. It is slow and will make mistakes.
What I did was: Offset(INDEX MATCH MATCH)CountA, CountA)
The point if this is that all I have to do to fill the table is to copy paste the name of the product I want to get the data from to the cell where the on the MATCH of the INDEX formula points to (in my graphs file that I am describing) and index matchmatch will find the right position for OFFSET to have the reference cell and offset will display the data I want. So it takes literally 10 seconds to make a table work.
So INDEX MATCH MATCH gets the OFFSET reference cell based on the date and name (of what I want to find) and then OFFSET gets the values I need for the table from the cell given by INDEX MATCH MATCH and by counting how many columns of data I need to display by using the COUNTA. COUNTA just counts until 5, maximum 6 on the table field by counting the columns I need to get for OFFSET to display, from the data source file.
So the heavy work is basically done by INDEX MATCH MATCH and OFFSET just fills the table with the numbers around the reference cell.
Again, this file will not be used for heavy calculations, it only displays graphs.
Do you guys think this may lead to a slow spreadsheet?
I will make a bunch of copies of the sheet with tables and graphs to see if it slows the file down. So far I've done some 40 tables and it works just fine.
OFFSET gets a lot of bad press so that's I need your opinion
Thanks!
Question: Will OFFSET slow down my spread sheet in this scenario?
The task is to create a table with data such as: date on the left, followed by columns with data corresponding to different metrics about a product I am searching. This info is being taken from an external excel file. One table is around 15 rows and 3-6 columns. I would have around 150 of these tables in my file with a grpah next to it.
This is a "terminal" file with charts and tables, it's not a model and no calculations take place in this file. It only has to get data from existing sources.
It's not easy to explain it here, but using INDEX MATCH ONLY for this would take me ages. I have to make a file with hundreds of graphs and each graph has one of these tables with data next to it. I would have to copy the index match formula and then manually change the row number where the data comes from (the external excel file) in-formula. It is slow and will make mistakes.
What I did was: Offset(INDEX MATCH MATCH)CountA, CountA)
The point if this is that all I have to do to fill the table is to copy paste the name of the product I want to get the data from to the cell where the on the MATCH of the INDEX formula points to (in my graphs file that I am describing) and index matchmatch will find the right position for OFFSET to have the reference cell and offset will display the data I want. So it takes literally 10 seconds to make a table work.
So INDEX MATCH MATCH gets the OFFSET reference cell based on the date and name (of what I want to find) and then OFFSET gets the values I need for the table from the cell given by INDEX MATCH MATCH and by counting how many columns of data I need to display by using the COUNTA. COUNTA just counts until 5, maximum 6 on the table field by counting the columns I need to get for OFFSET to display, from the data source file.
So the heavy work is basically done by INDEX MATCH MATCH and OFFSET just fills the table with the numbers around the reference cell.
Again, this file will not be used for heavy calculations, it only displays graphs.
Do you guys think this may lead to a slow spreadsheet?
I will make a bunch of copies of the sheet with tables and graphs to see if it slows the file down. So far I've done some 40 tables and it works just fine.
OFFSET gets a lot of bad press so that's I need your opinion
Thanks!
