Importing Variable PowerQuery Information into a Specific Cell

[kohltonyork]

Here is the link to my spreadsheet, will be needed to see what I’m doing (is a copy so changes can be made freely):

FD Sheet copy.xlsx

1drv.ms

I have been putting this spreadsheet together using many Power Queries to break down advanced statistics in NBA games. I have ran into a problem that I can’t seem to get around for whatever reason.

In my Slate Matchups sheet, you will see a table, in this case A1:B:11. The problem I am having is that the number of games change from night to night. In this case there is 10 games tonight. I need the information from A2 into K4. I need the information from B2 into M4. When scrolling down you will also see I need the information from A3 into K37 and B3 into M37. This continues for as many games as there are a night. Seems simple enough I tried putting =$A$2 into K4 respectively and it works. The problem occurs in the homemade tables created below.

Let’s say there is only 6 games the following night, only 6 matchups appear in the top right corner so the bottom 4 homemade tables go unused. That turns the formula used in the K and M columns into a #REF error and if the following night returns to a 10 game matchup I have to renter the formula for each #REF error.

How can I avoid this? The table starting at A1 is imported through a PowerQuery. I don’t know if that’s what’s causing the issue but it is a vital function I need from my spreadsheet. I appreciate any help I can get, thank you!