I am creating a register for some workshops to introduce people's personal information, organized by when these workshops are taking place.

I have six different spreadsheets (Monday, Tuesday, Wednesday, Thursday, Friday and Untimed) and each spreadsheet has between 12 and 15 tabs, each tab for a different workshop taking place on that day. These are the ones with the table that look like this:


In addition to that, I have a seventh spreadsheet that serves as an index: it has multiple tabs based on topics with multiple links each linked to one of the tabs mentioned above.


In order to have a better overview of how many places are left in each workshop without actually opening every single tab, I decided to include a little counter using the formula:

=18-counta(IMPORTRANGE("*the_spreadsheet_key_here*", "9.00 Workshop!B5:B22"))

The reason for the 18 is that there are 18 places in total in each workshop.

This formula has worked fine in the spreadsheets Monday, Tuesday and Wednesday but in the other three the counter always shows as if one of the lines were filled in (it shows 17 remaining spaces instead of 18).

I have double checked that I have selected the correct cells and even used the formula from this post to ensure there is no "invisible" content I am missing.

In the end I have solved it separating the formula in two:

=18-COUNTA(B5:B22) in the register to count the introduced names

=IMPORTRANGE("*the_spreadsheet_key_here*", "9.00 Workshop!E14")

in the index to import the number got with the previous formula

Even if it does work now, I would still like to know why having the formula together would only work in some spreadsheets and not in others.

Any idea?

  • 1
    Does the problem persist when the data is on another sheet of the same spreadsheet? Can you post a link to an example where the problem occurs? – user79865 Jul 14 '15 at 12:52
  • I have just changed the post explaining a bit more in depth the organization of my spreadsheets. I hope that helps. Thanks! – Irantzu Jul 16 '15 at 8:06
  • When debugging a formula with importrange, I recommend first using just the importrange command by itself, without the counta stuff. What happens when you do that, is there any difference between the spreadsheets? – user79865 Jul 17 '15 at 3:32
  • When I use the importrange on its own, it works fine in all the spreadhseets. I think that is why separating the formulas has worked. – Irantzu Jul 17 '15 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.