Google Sheets help

Factual Questions
1

So I’ve put together a spreadsheet tracking one of my fantasy baseball leagues. (I know what you’re thinking, and the answer is yes, I’m very popular at parties.)

So each week I pull in the rankings and underlying stats (or at least, I have since week 8). Today, I created a dynamic chart that changes based on the particular stat I want to look at. The problem is that I have no idea how it works. Let me try to recreate it with a little less noise:

Raw data (all numbers are cumulative):
Week | Team | Runs | HRs
1 | TeamA | 10 | 5
1 | TeamB | 8 | 6
2 | TeamA | 17 | 9
2 | TeamB | 14 | 10

In a separate tab, I have my dynamic chart. It has a drop down cell to select which specific stat I want to look at, and then this:

Team | 1 | 2
TeamA | (formula)
TeamB | (formula)

The formula is populated across for all teams and all weeks. Here it is (please assume all $ are where they need to be, I’m typing this by hand):

=filter(query(RawData!A:D, “SELECT “&SUBSTITUTE(ADDRESS(1, MATCH(, RawData!A1:D5, 0), 4), 1, “”)), RawData!A:A=B2, RawData!B:B=A2)

The filter I understand. I want to pull the number of runs (“”) from Team A (“RawData!B:B”) in Week 1 (“RawData!A:A). The MATCH portion figures out which column in RawData the drop down is referring to. The QUERY/SELECT/SUBSTITUTE turns the match (when isolated, returns the letter of the column) into a column designation, but I don’t know HOW it does that.

2

Damn - that formula got messed up because I used pointy brackets and discourse didn’t like it. Here:

=filter(query(RawData!A:D, “SELECT “&SUBSTITUTE(ADDRESS(1, MATCH(DropDown, RawData!A1:D5, 0), 4), 1, “”)), RawData!A:A=B2, RawData!B:B=A2)

3

I’m not sure what you mean by ‘column designation’, but I’m going to assume you mean A, B, C …

The ADDRESS function returns a cell address in the form of a text. In this case that’s going to be row 1, & whatever column is returned by MATCH. You say MATCH returns the letter of the column, so let’s say ADDRESS returns C1.

As I read your formula, SUBSTITUTE will then search cell C1 for a ‘1’, substitute a blank (“”) for that 1, leaving a ‘C’. Since you say the column letter was returned by MATCH, this would be a whole set of unnecessary steps so I’m probably misunderstanding the question. And it’s been a while since I programmed in Google, so I may be overlooking something. But I enjoyed trying to puzzle it out so I’ll post this anyway. :slight_smile:

4

I don’t believe you have copied the formula correctly.

MATCH function only looks at a single row or column, you have it searching in A1:D5. As written it should return an N/A

The FILTER portion of your formula is superfluous.
You are filtering the output of a QUERY function, the query function is capable of filtering, sorting, aggregating, etc.

5

I don’t think that’s right - if I take it out, the QUERY returns the entire column. FILTER narrows it down by team and week.

That very well may be right - I just don’t know how to do it.

6

Try these:

(the second and third are easiest to understand)