Source
r/excel•
TheMapKing

How can I reference a cell (using INDIRECT) to pull data from a column in a different sheet?

unsolved

I want to find how many games were won after a specific game number. The game data is in one sheet and my formula is in another. My current formula is =COUNTIFS(Games!D:D,"W",INDIRECT("Games!E" & A75):Games!E,A75)

Games is the name of the sheet with the game data in it.

A75 takes a number (the game number to count after).

I want to count the number of "W" in column E from cell number Games!E(A75) to the end of the worksheet.

I'm not sure what's wrong with my formula, but I get a #NAME? error.

Posted Apr 28, 2020, 8:22 PM UTC

15
Jayplac137•

It would be helpful to know what A75 is.

Your formula should look more like this:

=COUNTIFS(Games!D:D,"W",INDIRECT("Games!E" & A75 & ":E10000"), A75)
1
TheMapKingOP•

A75 is an empty cell that I can type a number into. For example, if I want to get the number of wins after game 80, I type 80 into A75. I tried your formula and now I get #VALUE error.

1
Jayplac137•

=COUNTIFS(Games!D:D,"W",INDIRECT("Games!E" & A75 & ":E10000"), A75)

The two ranges need to match in length. Try this:

=COUNTIFS(indirect(Games!D" & A75 & ":D10000"),"W",INDIRECT("Games!E" & A75 & ":E10000"), A75)
1
TheMapKingOP•

Good news, I'm not getting any errors! Bad news, I'm getting 0.

1
Jayplac137•

K. I see an error. Try this.

=COUNTIFS(INDIRECT("Games!D" & A75 & ":D10000"),"W",INDIRECT("Games!E" & A75 & ":E10000"), A75)

And I want to make sure you understand that your criteria for E:E is going to be what's in A75.... That doesn't make much sense to me.

1
excelevator2036•

Sample data?.. there is an easy solution here.. Why do you want to use INDIRECT? Post questions to solve, not poor solutions to mangle into solutions

0