2019 Team Competition Tracker/Google Sheet Central

[Niki Hawkes, I made it past GOTM... barely]

Lmao on the YouTube things. Dying

[Timelord Iain, Tech Support]

Niki Hawkes wrote: "Lmao on the YouTube things. Dying"

The little animated birds really sell the comparison, I think :)

[Anni]

Timelord Iain (Eeyawn) wrote: "Niki Hawkes wrote: "Lmao on the YouTube things. Dying"

The little animated birds really sell the comparison, I think :)"

As long as you can dance as gracefully as the man in the video, everything's fine Iain :)

[Virginie]

Timelord Iain (Eeyawn) wrote: "Olivia wrote: "Gesundheit?

Is basically all I could think :D"

Can't let Virginie hog all the complicated formulas... I thought all her $ last year were bad, but we've definitely upped it a notch..."

...Strengthening my hold on the Spreadsheet Empire with Ultra-complicated-formulas magic AND pushing its borders to encompass the BR Requests land.

Also secured the help and assistance of the formerly neutral Timelord. Yup. Evil plan is working well.

[Niki Hawkes, I made it past GOTM... barely]

Yup definitely a common thread hahaha

[Timelord Iain, Tech Support]

I changed the formula in my hidden column from

=IF(E42="", "",MONTH(E42))
to
=IF(ISDATE(E42),MONTH(E42),"")

This let's me keep using the date column the way I like to (using wordy dates to track start dates, and Reading for tomes I've started, without throwing #VALUE errors in the hidden month column... then I no longer need to use special conditions to use the month column to trigger other formulas (like some of my graphs & monthly tallies)...

I made this change, because i'm reimplementing my monthly avg ratings with a new hidden column that parse stars into numbers)

[Virginie]

Oooh, good one. I didn't know ISDATE was a thing!
Though I should have realized, since I've been using ISNUMBER for the audiobook % thingy....

:p

[Timelord Iain, Tech Support]

Virginie wrote: "Oooh, good one. I didn't know ISDATE was a thing!
Though I should have realized, since I've been using ISNUMBER for the audiobook % thingy....

:p"

generally, you can just type =IS and get a list of suggestions on how to finish the formula... just like most formulas have =formulaIF version, that only does the formula based on criteria...

[Virginie]

Yup, I'm typing the beginning of a function just to see if it exists sometimes. The helper thingy is amazing for that :)

[Angela Jr.]

Quick question: is it possible to add a rereads/new books tracker, next to the other two trackers i have on my sheet?
And I'd love if e-books could also be added to the audiobook/physical book tracker.
Since i have no idea how to use sheets except type in book info, could someone (virginie? Iain?) have a look at it? Thanks in advance :)

[Angela Jr.]

(If you need space use the graphic novel column. Don't need that one ;) )

[Em Lost In Books]

Please look into graph on my sheet. I put completion date 01/26/2018 for Pawn of prophecy. Graph adjusted itself and now that I have corrected the date, that dot is still stuck in 2018...

[Timelord Iain, Tech Support]

Em Lost In Books wrote: "Please look into graph on my sheet. I put completion date 01/26/2018 for Pawn of prophecy. Graph adjusted itself and now that I have corrected the date, that dot is still stuck in 2018..."

The 2018 dot on your sheet is for Geekerella, which says you read it on 1/8/2018 instead of 1/8/2019...

[Seán]

So I'm just snooping around other people's sheets (as you do), and noticed that Arkadeb has a 7000 page book listed as having been completed this month? It looks like it's a web serial and so the page count probably isn't at all accurate. Just wanted to point it out as something strange to look at, as it looks like something easy to exploit.

[Anni]

Seán wrote: "So I'm just snooping around other people's sheets (as you do), and noticed that Arkadeb has a 7000 page book listed as having been completed this month? It looks like it's a web serial and so the p..."

Nope that's correct, it's just a reeeeally long book, some of us even doing a Buddy Read right now :)

[Charlie]

I have a quick question, for the Way of Kings BR I'm reading two separate books The Way of Kings, Part 1 and The Way of Kings, Part 2 instead of The Way of Kings. Should I log the two books I'm reading on my spreadsheet or just add the american copy that has both parts in one book?

[Anni]

Charlie wrote: "I have a quick question, for the Way of Kings BR I'm reading two separate books The Way of Kings, Part 1 and The Way of Kings, Part 2 instead of [book:The Way of King..."

As you'll get more points for the american copy, I would add that one ;)

[Topher]

I have a feeling I’m missing out on something but just can’t put my finger on it.

[Timelord Iain, Tech Support]

Seán wrote: "So I'm just snooping around other people's sheets (as you do), and noticed that Arkadeb has a 7000 page book listed as having been completed this month? It looks like it's a web serial and so the p..."

To reiterate what Anni said, the "book" is 1.7 million words, which is about the length of the first 5 Wheel of Time books combined, or the 3 existing Stormlight Archive books + Edgedancer + the next Stormlight book...

The page count might be slightly inflated, but the fan-made audiobook is 180hrs, and a bunch of people are "trying" to read it over in the BR thread... the "book" is split into 31 Arcs, which start off the length of a novella each, and grow to be book-length as the story progresses...

7000 pages is the longest edition in the top 5... the lowest/most popular edition is 6680 pages... not much different, relatively...

[Seán]

Sir Anni wrote: "Nope that's correct, it's just a reeeeally long book, some of us even doing a Buddy Read right now :)"

Interesting, I'm just looking into it a bit and it looks cool enough. Might start reading it in the background and have it slowly ticking away over the year :)

[Charlie]

Sir Anni wrote: "Charlie wrote: "I have a quick question, for the Way of Kings BR I'm reading two separate books The Way of Kings, Part 1 and The Way of Kings, Part 2 instead of [book..."

Haha thanks Anni I will!

[Timelord Iain, Tech Support]

You guys didn't fiddle with the Scoresheet fonts did you?... I think there was a Google Sheets backend update that changed the font being used for tab names & menus... I don't particularly like it... I was used to the old font...

[Charlie]

I haven't even noticed! Shows how much attention I pay!

[Timelord Iain, Tech Support]

Charlie wrote: "I haven't even noticed! Shows how much attention I pay!"

They switched it to their special google font, that makes everything smooth and circular, without the little flourishes you see in other fonts... also makes the letters take up more space... it's actually caused some menu options to word wrap onto 2 lines....

[Siobhan]

I haven't touched it! :) I like you font you guys had it set as.

[Timelord Iain, Tech Support]

Siobhan wrote: "I haven't touched it! :) I like you font you guys had it set as."

It's not the fonts being used in the sheets... it's the fonts in the menus and tab names at the bottom... looks like that's fully controlled by google... I found some information online about rollouts from January 15th thru Febuary 11th to all services...

[Diana Stormblessed]

Someone tell me what's wrong with my sheet. Sometimes I go in and my rank and team rank show as N/A, and sometimes it's fine. Do you guys know why? Or what I can do to avoid the NA?

[Timelord Iain, Tech Support]

Diana Stormblessed wrote: "Someone tell me what's wrong with my sheet. Sometimes I go in and my rank and team rank show as N/A, and sometimes it's fine. Do you guys know why? Or what I can do to avoid the NA?"

I've noticed that as well... it just seems to take the sheet some time to recalculate the rankings every time someone updates their points...

[Diana Stormblessed]

Oh, so it's not just me?

[Scott , Karsa Orlong]

I accidently clicked on last year's scoreboard and it took me a minute to figure out WTF was going on. Laughing at myself now.

[Siobhan]

Scott wrote: "I accidently clicked on last year's scoreboard and it took me a minute to figure out WTF was going on. Laughing at myself now."

Hahaha 😂😂

[Diana Stormblessed]

Hey, you know how the spreadsheets now have a drop down for pages/day, point/day, etc, can I get a drop down added for average pages/book? Or is that too much?

[Timelord Iain, Tech Support]

Diana Stormblessed wrote: "Hey, you know how the spreadsheets now have a drop down for pages/day, point/day, etc, can I get a drop down added for average pages/book? Or is that too much?"

I can whip something up tomorrow... I have a similar formula in my secondary monthly info row... along with average monthly rating... number of different authors/series I read, etc...

[Diana Stormblessed]

Thanks!

[Elinor]

O Mighty Spreadsheets gods, I have a problem ! The last two books I added have bugs : the first one is a manga of 192 pages but is giving me 100+ points, and the second one is saying #REF ! What can I do to fix it ?

[Timelord Iain, Tech Support]

Elinor Master of Gifs wrote: "O Mighty Spreadsheets gods, I have a problem ! The last two books I added have bugs : the first one is a manga of 192 pages but is giving me 100+ points, and the second one is saying #REF ! What ca..."

Somehow the formula got screwed up... was pulling from D26 instead of E25... so it thought your book was 2/08/2019 pages long... the #REF problem was also a D26 issue... usually happens when changes are made to rows/columns used by formulas... not sure what caused this one exactly... but it's all sorted...

[Elinor]

Thanks ! I try and not change anything because I'm afraid I will break everything if I do...

[Timelord Iain, Tech Support]

Diana Stormblessed wrote: "Thanks!"

Finally remembered... thanks to Elinor... all sorted (first time I just gave you a second copy of pages per day... oops... but I fixed it :)

[Timelord Iain, Tech Support]

Elinor Master of Gifs wrote: "Thanks ! I try and not change anything because I'm afraid I will break everything if I do..."

Generally, you can figure out what's wrong with the common formulas by just comparing the 2 above, and seeing what changes (usually it's just the row numbers... E/F/G25 becomes E/F/G26 becomes E/F/G27... the harder formulas to fix are the top ones that only happen once :)

[Veronica]

*sigh* This is why I hate technology. It hates me first.

I used the entry forms to submit nine books but the points never showed up on my spreadsheet tab. Then I entered all nine books directly onto my spreadsheet and now a couple of the columns have huge ERROR messages on them.

[Anni]

Veronica wrote: "*sigh* This is why I hate technology. It hates me first.

I used the entry forms to submit nine books but the points never showed up on my spreadsheet tab. Then I entered all nine books directly on..."

Looks like someone already fixed it :) There was only one ERROR left in the B2, which is gone now as well. I still have no idea why the entry form hasn't worked...

[Barry]

Yeap, I tried that too once. I guess, just use the google sheet directly.

[Timelord Iain, Tech Support]

SO... I fixed "everything"

I wish Virginie was here (starting to sound like a broken record, after a month absence)...

Anyway... some things in the Sheet were hardcoded that should really have been dynamic (in all fairness, they were dynamic in some spots and hardcoded in others... probably a case of too many cooks in the kitchen)

I started changing things to fix them, only to realize I was making them worse... the real problem, was that the ordering of people in the Form Calculations page didn't match the Participants Data page... this seems to have happened when Frank joined the competition, and because we never shifted Bill's sister down with the other Jessica's once we got her name...

So now, the Form Calculations should work again... and because of that, I hit the checkboxes to disable the calculations on the 10 existing entries (Veronica today and Niki last month) since they reinput them in their personal sheets already...

To future-proof this some more, I/we need to go through and copy the QUERY formula in the hidden column at the far right for everybody, so it's no longer hardcoded (Frank has a good example again)... lot of clicking there, accessing every sheet, unhiding the column, adding the formula, and rehiding...

if people want to help, it's the hidden last column of your sheet... the first cell should have a number between 2 & 56, and below it is #gid=NUMBERS

if the cell with the number (2-56) doesn't look like THIS, copy it in: =QUERY('Participants Data'!$A$2:$C61, "Select A Where C ='"&A1&"'")

All this does is find your name in the Participant Data sheet and copy over the row number (which we implemented in another hidden column)... this means we can resort the data alphabetically, any time there is a new person added to the competition... doing that at some point seems to be what broke everything...

[Veronica]

Thanks, Iain...even though I'm pretty sure I have no idea what you just said. I'll have my husband take a peek and try that thing you mentioned. I'm afraid to touch the spreadsheet anymore.

[Timelord Iain, Tech Support]

Can probably just worry about it the next time a problem arises... the important thing is to take the best version of everything when we duplicate the sheet for next year...

[Diana Stormblessed]

Timelord Iain (Eeyawn) wrote: "SO... I fixed "everything"

I wish Virginie was here (starting to sound like a broken record, after a month absence)...

Anyway... some things in the Sheet were hardcoded that should really have be..."

Speaking of, where is Virginie? I've been getting worried about her.

[Bea]

Diana Stormblessed wrote: "Timelord Iain (Eeyawn) wrote: "SO... I fixed "everything"

I wish Virginie was here (starting to sound like a broken record, after a month absence)...

Anyway... some things in the Sheet were hardc..."

Yeah, me too. I hope she's alright.

[Timelord Iain, Tech Support]

Diana Stormblessed wrote: "Timelord Iain (Eeyawn) wrote: "SO... I fixed "everything"

I wish Virginie was here (starting to sound like a broken record, after a month absence)...

Anyway... some things in the Sheet were hardc..."

No idea... me and Anni have been worried... I left a comment on her BRT thread awhile back... she hasn't been on here in a month...

EDIT: Just thought to send her an email (only have it because of spreadsheet permissions emails, similar to Rob)... hopefully she responds...

[Siobhan]

Let us know if you hear? Hope she's alright.

[Timelord Iain, Tech Support]

Will do