Selected Answer
Amin
Here's a non-VBA solution which should work. It uses a hyperlink to jump to page 2 where cell A1 will show the value of (yellow) cell A3 sheet 1 (Select a product) and I assume you will extract a report based on that.
It uses data validation and the CELL function. Cell A3 has List data validation based on the table ("Products") from cell L3 down (which can easily be extended). The validation formula is:
=INDIRECT("Products[Product list]")
where INDIRECT points to the data in that table.
Cell A3 then has a dropdown list, by which you can pick a product. It also has a single hyperlink which points to cell A1 of sheet 2 ("Report") where the formula is:
=CELL("contents",'Select a product'!A3)
where the bold part tells Excel to show what's in the non-bold second argument i.e. A3. on the other sheet. The first argument of CELL ("contents") is one of the "info-types" that can be used by this function.
(Revision 17 Oct 2023: in fact you don't need CELL here, You can just use = and point to A3 on the other sheet as usual. It's in my file since I was initially using the HYPERLINK function to point to one of several sheets).
From there I assume you will provide the detail needed on sheet 2 (via Power BI or whatever).. Note that cell G1 has a hypelink to jump back to the product select sheet.
Another alternative is a solution I posted before where buttons are used to navigate to individual sheets, see Viewing Excel Workbook on a website. (A similar apporach is possible for many items but using hyperlinking and CELL, without buttons).
Hope this fixes your problem. If so. please remember to mark this Answer as Selected.
