Chart over multiple sheets with differently sorted rows

Question

I have a Sheet file for tracking the prices of graphics cards over time. Each sheet is a timestamp of sorts, with the GPU Model in Column A and its price in Column E, with a bunch of other data in the other columns. Each sheet is sorted by an arbitrary index number (the model's Benchmark score in Column B divided by its current price in Column E).

Creating a chart with data from these multiple sheets works, however, the chart assumes that the rows are immutable, ie. if a GPU Model A is on Row D in Sheet 1, the chart assumes it is always on this row in all sheets, even though this might not be the case. Also, some models have not been tracked until eg. Sheet 5 (models not yet in stores), and some since eg. Sheet 7 (too old for example).

As a bonus question it would be cool to get a 'highlighting' functionality where the line in the graph under the cursor would get highlighted and all the other ones dimmed.

Answer 1

Here's one approach you may test out:

1. list/update all the tab names in Column_A

2. this formula(in Cell_B1) updates the unique models list from all tabs

   =unique(tocol(map(tocol(A:A,1),lambda(Σ,torow(indirect(Σ&"!A:A"),1))),1))

3. Create a drop-down list based on the list available in Column_B

4. this formula(in Cell_D1) auto-updates a graph-compatible-table format of tab_name & nominal price value(searches for this header in each tab)

=reduce(torow(,1),tocol(A:A,1),lambda(a,c,hstack(a,vstack(c,ifna(xlookup(C1,indirect(c&"!A:A"),choosecols(indirect(c&"!A:Z"),xmatch("Nominal Price",indirect(c&"!1:1"))),))))))