2018 Team Competition Tracker/Google Sheet Central

[Liam]

Niki Hawkes wrote: "Liam wrote: "Niki Hawkes wrote: "I predicted no-show probability when assigning teams, so I'm actually glad to see our participation numbers relatively even with 8,8,9, & 10 (which without lovely J..."

Ahhhh yes I see. It's inevitable, unfortunately haha.

[Rob]

Team Scoreboard now available :)

[Bill]

Each year only about 15% of the people who sign up for the general goodreads reading challenge complete it. So thinking we are doing pretty good on participation.

[Choko]

Rob wrote: "Team Scoreboard now available :)"

Dude, you are doing an amazing job!!! Thank you for all the effort and everything:):):) From us, the ones who have no idea how to do stuff like that, you seem like a magician:):):):)

[Rob]

Choko wrote: "Rob wrote: "Team Scoreboard now available :)"

Dude, you are doing an amazing job!!! Thank you for all the effort and everything:):):) From us, the ones who have no idea how to do stuff like that, ..."

I have to access my warren of spreadsheet chaos. Using that power is addictive.

[Liam]

Rob wrote: "Team Scoreboard now available :)"

So cool! Thanks Rob!

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

I love it!! Thanks Rob!

And shit, I'm in last place.

[Angela, I'm Silverfox lost]

I'm not too worried yet about team participation as I haven't added anything, given I finished two books I start in December so they didn't count for this challenge and my first book for the challenge is a Malazan book.

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

Angela wrote: "I'm not too worried yet about team participation as I haven't added anything, given I finished two books I start in December so they didn't count for this challenge and my first book for the challe..."

That's a good point. No points yet doesn't necessarily mean they're not participating.

[Liam]

Niki Hawkes wrote: "I love it!! Thanks Rob!

And shit, I'm in last place."

Yeah, honestly I was expecting a lot more from our leader ;]

[Choko]

Rob wrote: "Choko wrote: "Rob wrote: "Team Scoreboard now available :)"

Dude, you are doing an amazing job!!! Thank you for all the effort and everything:):):) From us, the ones who have no idea how to do stu..."

That is one very mysterious and foreboding warren!!! I guess you are a master in it:):):)

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

Hahaha oh, it's sooo on! ;P

[Choko]

Don't stress about the competition - we all have good and bad months... I always start like gang-busters and have almost no production around the holidays... Just how it is...

[Mayim]

Rob wrote: "Choko wrote: "Rob wrote: "Team Scoreboard now available :)"

Dude, you are doing an amazing job!!! Thank you for all the effort and everything:):):) From us, the ones who have no idea how to do stu..."

Warren of spreadsheet chaos is my new favourite warren.

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

I can't tell you how delighted I am that I now know what that is referencing. :)

[Rob]

Mayim wrote: "Rob wrote: "Choko wrote: "Rob wrote: "Team Scoreboard now available :)"

Dude, you are doing an amazing job!!! Thank you for all the effort and everything:):):) From us, the ones who have no idea h..."

Yes, "Even poor Kruppe is confused and confounded by this most efficacious warren despite his prodigious knowledge". Or something like that.

[Rob]

Niki Hawkes wrote: "I can't tell you how delighted I am that I now know what that is referencing. :)"

Malazan is an excellent way to rise in the standings Niki, just sayin'!

[jess]

Warren-love.
Awesome.

[Rob]

Mayim wrote: "Warren of spreadsheet chaos is my new favourite warren.

Also known as Kurald oi vey.

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

On pg 66 of GotM as we speak! :)

[Rob]

Niki Hawkes wrote: "On pg 66 of GotM as we speak! :)"

Woohoo! Welcome aboard.

[Hacen]

Iain wrote: "You need to up your game... that new stat showing which team member is raising the score the most isn't doing your team any favors... you need a Hacen.. she's the only person with 40+ points that s..."

Haha thanks Iain 😁

[Tammie]

Angela wrote: "I'm not too worried yet about team participation as I haven't added anything, given I finished two books I start in December so they didn't count for this challenge and my first book for the challe..."

Same here. My first two books completed this month were started in December.

[Timelord Iain, Tech Support]

I made some modifications to my scorecard:

I finally moved my challenge criteria descriptors to their own column off to the side, since I'm planning to read White Sands and need the Graphic Novel column for it's original purpose...

I shortened a bunch of column names where they were the only thing keeping the column width so long...

I added the ratings columns we discussed, and even did some formulas for it... if you put a 1/2/3/4/5 in the # column, it will generate visual ★★★★★s in the RATING column... and I added an average at the top like I did with page counts...

I also added in links to my challenges and trackers in the upper left, since we're barely using the tally threads anymore... for confirmation purposes... and ease of me going to the page to update things occasionally...

I'm not sure if people can copy these things directly from my locked spreadsheet to their own, but here are the relevant formulas:

=REPT("★", MIN(MAX(0, M4), 5)) makes the star symbols

and

=AVERAGE(M4:M1000) is the simple averaging formula... just need to change M to the relevant column...

and you can copy that formula or similar into the first book row of your spreadsheet, then you highlight the cell and drag the little square in the lower right to copy the formula all the way down... and the spreadsheet changes the important part to M5/M6/M300/etc... or Rob can do this for everyone if he's so inclined...

[Timelord Iain, Tech Support]

Is there an easy/fast way change the background color of every other row (but not the cells already a special color)... to make it easier to read along a row from the book name through to the ratings at the other end of the screen...

My short term solution was to just copy the book name column over to the right side of the screen, but the recoloring would be a better solution

EDIT: I figured it out:

Click on cell A4 -> Format menu -> Conditional Formatting -> Ranges: A4-F:300 & K4:N300 -> Custom Rule: =ISODD(ROW()) -> Light yellow fill option -> DONE

[Miche]

If you set the area as a table you can choose from templates with different formats and colours. I do that when I am working on big accounts reconciliation, to avoid eye strain.

[Miche]

Just make sure to select all columns, or you will mess up your data if you use a filter later on.

[Lundos]

Hi Iain

You can use this formula:
=MOD(ROW(),2)=1

Select your cells, go to 'Format' and 'Conditional formatting'. Choose formula and paste it in. Then you can choose between colours.

EDIT: See example in my sheet for my books.

[Timelord Iain, Tech Support]

Thanks guys, conditional formatting did the trick... there's an ISODD and ISEVEN formula... I just needed to figure out I could use cell ranges that avoided columns GHIJ...

[Rob]

Cool stuff guys. I'll look into it more later.

[Margret, Caladan Brood Face]

I have no idea what language you guys are speaking lol

[Choko]

Margret wrote: "I have no idea what language you guys are speaking lol"

Me neither!!! And Iain's looks sooo pretty now:):):)

[Rob]

Choko wrote: "Margret wrote: "I have no idea what language you guys are speaking lol"

Me neither!!! And Iain's looks sooo pretty now:):):)"

I'll copy it over for everyone (or Iain and Lundos can help too). Already did mine :)

The best way to start is to:

Duplicate my or Iain's tracker. Clear out all the non-formula columns. Copy over your books, dates, etc from your tracker. Delete your tracker. Rename the duplicate tab to what your original tab as was and main scorecard should update fine.

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

I think whatever you did messed with all the stat charts.

[Margret, Caladan Brood Face]

Ok..... uhhhh what are we doing I don’t understand

[Rob]

Rob wrote: "Choko wrote: "Margret wrote: "I have no idea what language you guys are speaking lol"

Me neither!!! And Iain's looks sooo pretty now:):):)"

I'll copy it over for everyone (or Iain and Lundos can ..."

thanks, easily fixed. Just had to click on the instance where my sheet was linked.

[Rob]

Rob wrote: "Rob wrote: "Choko wrote: "Margret wrote: "I have no idea what language you guys are speaking lol"

Me neither!!! And Iain's looks sooo pretty now:):):)"

I'll copy it over for everyone (or Iain and..."

And no worries Marg. you don't have to do a thing.

[Choko]

Margret wrote: "Ok..... uhhhh what are we doing I don’t understand"

Girl, I don't feel so bad, since I know there is another person out there who has no idea about any of this too:):):)

[Margret, Caladan Brood Face]

Like I don’t even have any idea what we are fixing or whatever LMAO.

[Tammie]

Choko wrote: "Margret wrote: "Ok..... uhhhh what are we doing I don’t understand"

Girl, I don't feel so bad, since I know there is another person out there who has no idea about any of this too:):):)"

I don't either, this has gotten too complicated for me. I really don't want to bother with spread sheets. My little score card is enough for me, lol.

[RuinEleint]

So, umm, given that I have forgotten what little I knew about Excel formulas, can I assume that the sheet is still ok for inputting page numbers, and getting scores?

[Rob]

Arkadeb wrote: "So, umm, given that I have forgotten what little I knew about Excel formulas, can I assume that the sheet is still ok for inputting page numbers, and getting scores?"

Yep. Iain just made a little fancier so you can rate your books (1 to 5 stars, did some colors, and put links in his so he could jump right to his own challenge trackers. I can easily just leave sheets as is and they'll still work fine.

[Rob]

Tammie wrote: "Choko wrote: "Margret wrote: "Ok..... uhhhh what are we doing I don’t understand"

Girl, I don't feel so bad, since I know there is another person out there who has no idea about any of this too:):..."

You can just keep it as is Tammie - book, page number, date completed, I don't have to copy and paste the new sheet on. Its just has more bells and whistles.

[Veronica]

I think this has gotten way more complicated than it needed to be, lol.

[Rob]

Veronica wrote: "I think this has gotten way more complicated than it needed to be, lol."

Probably so. I probably kept it simple in the first place just it wouldn't be too overwhelming. Spreadsheet customization can be addictive once you're into it (strange I know), but ooh, how do I do this what if I do that..etc).

I'll just keep them nice and simple like the original. If you want the bells and whistles version ask.

[Hacen]

Can mine go back to normal? I don't need all the extra stuff.

[Rob]

Hacen wrote: "Can mine go back to normal? I don't need all the extra stuff."

No problem.

Dammit Iain, stop being all creative and stuff :)

I put everyone back to the original (except Iain and I might keep it that way too). Google sheets seems to have an unlimited undo (YES!).

[Sha]

Ooooooooh can I have the custom features too please? I don't need the colors but the stars would be good

[Sha]

Wait never mind I can set it up myself with simpler commands.

Man all those charts and stuff make me want to go learn more of excel. That looks like fun~

[Timelord Iain, Tech Support]

Sorry... I made the changes to my sheet cause I liked them and to ease readability as I'll be reading so many books... and then tried to explain how to copy them if anybody was so inclined... the original sheet does all that's really needed for the challenge...