CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Vasu Gupta
Vasu Gupta

Reputation: 5

How to import data from a sheet where sheetname is dependent on a cell value

I have been trying to make a Google Sheet for some purpose which shall have details of various movies. Here is the link: https://docs.google.com/spreadsheets/d/10-4BWaK-zmTxmqr2ov7AvjQW-wAFR8NKXkICuCK1dTY/edit?usp=sharing

Now I wish to count the number of movies per year where sheet name is dependent on cell value. For example, in Sheet: "Data" I wish that B2 shows the count of movies in Sheet named '2019' but I don't want it to be written as '=COUNTA('2019'!A:A). Instead I wish that the sheet name automatically gets picked up from cell A2. So if I change the year in A2, the Count in B2 should change automatically.

I tried using INDIRECT and MATCH function as =COUNTA(INDIRECT(A2,false)) and =MATCH(A2,INDIRECT(A2),1) but I think I made some errors.

Also in the same way I wish to count in C2 but with a condition: to count only those cells from G column where value is watched

Upvotes: 0

Views: 58

Answers (1)

JPV
JPV

Reputation: 27262

In B2 try

=sumproduct(INDIRECT(A2&"!A3:A")<>"")

and see if that works?

Upvotes: 1

Related Questions