Google Sheets - Transpose row to new column based on cell value

Question

I have this very obtuse spreadsheet I've been working

Currently I'm using a Transpose row G:P to a column in my second sheet and I want it so the D25 value will affect the range of the Transpose in D14, and whenever D25 is changed, the transpose will update accordingly!

So instead of editing manually $G326:$P326

If D25= 856

It'll change to: $G856:$P856

Here's an example of what I've been working with: https://docs.google.com/spreadsheets/d/1rSbh5KN1ZHz8fF6edP1TSj3sMyBCwGqB4lv_AJ5wAC8/edit?usp=sharing

From what I've read it should be plausible, but I'm not knowledgable enough to get Query to work despite multiple attempts, so any advice is much appreciated

Answer 1

You may try:

=tocol(chooserows('Card List (Automated Skills)'!$G:$P,D25))