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.