CODEWITHSUNDEEP
google-sheetsexcel-indirect
user2705241
user2705241

Reputation: 49

Using INDIRECT to reference another sheet and current sheet (Google Sheets)

I am working in Google Sheets, not excel.

I am currently working (and the below formulae appear) on Sheet1. In Sheet2, I have additional data. On Sheet1 in cell G1 is "Sheet2" (without the quotes). Cell A3 that I am referencing is on Sheet1.

This formula works just fine:

=IFERROR(SUM(FILTER('Sheet2'!N:N; 'Sheet2'!C:C=A3)), "--")

This formula also works fine:

=IFERROR(SUM(FILTER(INDIRECT("'"&$G$1&"'!N:N"); 'Sheet2'!C:C=A3)), "--")

However, this formula does NOT work and returns a blank cell (not an error or n/a. Just blank).

=IFERROR(SUM(FILTER(INDIRECT("'"&$G$1&"'!N:N"); INDIRECT("'"&$G$1&"'!C:C")=A3), "--"))

It appears that my second INDIRECT statement is not working with the "=A3". I've tried putting the quotes in different places and moving around the parentheses, but nothing seems to work.

Upvotes: 2

Views: 4813

Answers (1)

zolley
zolley

Reputation: 6060

I think you have accidentally put some closing paranthesis in wrong order. The correct formula should look like this:

=IFERROR(SUM(FILTER(INDIRECT("'"&$G$1&"'!N:N"), INDIRECT("'"&$G$1&"'!C:C")=A3)), "--")

Look at the final part of the formula, there is the difference (count the number of the closing parenthesis).

Should work, hope it helps.

Upvotes: 1

Related Questions