I have designed up a sheet that has a search based PQ. When you first open the sheet it automatically runs a "Refresh All Sheets" for the Power Queries. This shows a full list of all .CSV files in a specified folder (C:\CSV\). The user can then select one of these .csv files to "import". When you Refresh the queries again it generates 3 tables and this is where the problems occur.
The resultant cells in one of the Tables grow and there are only 6 cells in total (excluding the headers) for this table. The initial table size is BC2:BE3, Row 2 is the headers and Row 3 is the first cell in the table this is BC3:BE3, once the refresh Queries is performed the Table adds one additional row which is BC4:BE4. BC3, BC4, BD4 and BE4 are all supposed to be absolute cells that are referenced on another page. If I clear the search cell and refresh Queries, the absolute cell reference that looks at BC3 returns a !#REF!
I have been unable to rectify this issue and I have a massive data set that returns information for 2 other tables that would do exactly the same thing. I've tried naming the cells individually, and I've tried using the Table names and column referencing as well. The 2 larger data tables also do something very strange in that it always returns the last row of the table. Even if I tell it to look in A4 it always returns the last row of the resultant table, so it returns A343. Sidenote, the Power Query returns the full table on a separate sheet.
The resultant cells in one of the Tables grow and there are only 6 cells in total (excluding the headers) for this table. The initial table size is BC2:BE3, Row 2 is the headers and Row 3 is the first cell in the table this is BC3:BE3, once the refresh Queries is performed the Table adds one additional row which is BC4:BE4. BC3, BC4, BD4 and BE4 are all supposed to be absolute cells that are referenced on another page. If I clear the search cell and refresh Queries, the absolute cell reference that looks at BC3 returns a !#REF!
I have been unable to rectify this issue and I have a massive data set that returns information for 2 other tables that would do exactly the same thing. I've tried naming the cells individually, and I've tried using the Table names and column referencing as well. The 2 larger data tables also do something very strange in that it always returns the last row of the table. Even if I tell it to look in A4 it always returns the last row of the resultant table, so it returns A343. Sidenote, the Power Query returns the full table on a separate sheet.
