2018 Team Competition Tracker/Google Sheet Central

[Timelord Iain, Tech Support]

I didn't even notice... maybe it wasn't me... I only fiddled for a few minutes... did a google search... and tested on a blank stretch of my own sheet... then went back and put lena's sheet back how I found it...

[Virginie]

What? My childish prank went unnoticed?!? NOOO! 😂

[Diana Stormblessed]

Virginie, you were hiding rows since Jan? You mean I wasn't the first one to hide rows? Bummer. I was going to take out a copyright on that.

[Rob]

Iain wrote: "Looking at Lena's sheet I started looking for ways to make my scorecard more manageable as a Power Reader

By this, I just mean I read so much that I need to scroll down 4-5 pages just to update my..."

Awesome. Just did this for Julia's page. That saves time.

[Virginie]

Diana Stormblessed wrote: "Virginie, you were hiding rows since Jan? You mean I wasn't the first one to hide rows? Bummer. I was going to take out a copyright on that."

Hehe! I just hate scrolling :p

[Diana Stormblessed]

Virginie wrote: "Diana Stormblessed wrote: "Virginie, you were hiding rows since Jan? You mean I wasn't the first one to hide rows? Bummer. I was going to take out a copyright on that."

Hehe! I just hate scrolling :p"

I feel you there. Once my books fall off longer than a page I was over it. Plus I wanted a summary so I could see how I was doing month by month and once I was done with reading for those months there was really no reason to keep the details visible.

[Virginie]

Yeah. I reached that point end of January :p (Yay mangas!)
Until a few days ago, I only had an automated "current month" tracker but I wanted to compare my monthly stats. I was doing like Lundos before (a table on the side) but hiding rows was hiding it as well... So the monthly headers idea, I stole from you ;)

[Diana Stormblessed]

Virginie wrote: "Yeah. I reached that point end of January :p
Until a few days ago, I only had an automated "current month" tracker but I wanted to compare my monthly stats. I was doing like Lundos before (a table ..."

Glad I can help. :-) Coloring my alternating rows I stole from you, so it's a nice little round of google spreadsheet theft we got going :-)

[Virginie]

:)

I actually learned some cool formulas and tricks thanks to this competition (and my Excel mentor, of course ;) )

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

I may or may not have unhip her rows yesterday trying to figure out where the data went and how to do it. Only once tho :)

[Virginie]

Haha. And here I was, convinced someone was undoing my work on purpose and "fighting" back... It was just Iain and you trying to figure out my Excel shenanigans xD

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

Lmao yeah probably ;P

[Jenna, I'd be free if not for Temper & Edgewalker]

Hi Rob! Could you add some new teammates to the Jaghut Guardians team?
Iniya and Sedat will both be joining the team.

Thank you so much!

[Em Lost In Books]

@Virginie: hello! i want few changes in spreadsheet. can you help? :)

[Virginie]

Sure! What do you want?

[Em Lost In Books]

have to scroll down a lot so can i have only month and total rows and all the rows with books hidden?

[Em Lost In Books]

thanks a bunch! :D

[Virginie]

Done! Do you want to keep your monthly tally on different rows or all in one (like I did for January)?

[Em Lost In Books]

okay.

[Em Lost In Books]

Virginie wrote: "Done! Do you want to keep your monthly tally on different rows or all in one (like I did for January)?"

thanks, again. you are the best!! :D <3

[Virginie]

You're welcome :)

[Rob]

For anyone thinking about next year, I did a little quick and dirty math based on today's standings. Lower left-hand side of the team scoreboard sheet. Took the total points for all of us, divided by 4 to get an average. then reassigned people based on their progress this year to get more even results. I'm sure it'll change over time, but we'll be better able to assign people to teams now that we have a better understanding of what pace people read at.

I"m just messing around. Not an official resorting of people by any stretch of the imagination. (I don't have that authority!)

[Jenna, I'd be free if not for Temper & Edgewalker]

Rob wrote: "For anyone thinking about next year, I did a little quick and dirty math based on today's standings. Lower left-hand side of the team scoreboard sheet. Took the total points for all of us, divided ..."

Cool! Will have to check that out. Also, not sure if it got lost in the comments or if you already saw it, but this post is a couple messages up from me:

Hi Rob! Could you add some new teammates to the Jaghut Guardians team?
Iniya and Sedat will both be joining the team.

Thank you so much!

[Rob]

Jenna wrote: "Rob wrote: "For anyone thinking about next year, I did a little quick and dirty math based on today's standings. Lower left-hand side of the team scoreboard sheet. Took the total points for all of ..."

Sure - just saw this. All set. Let me know if you have any issues!

The Blood Eagles need more new blood! (no we're not going to drink it).

[Jenna, I'd be free if not for Temper & Edgewalker]

Rob wrote: "Jenna wrote: "Rob wrote: "For anyone thinking about next year, I did a little quick and dirty math based on today's standings. Lower left-hand side of the team scoreboard sheet. Took the total poin..."

Thank you!

...and please don't drink blood O_o

[Timelord Iain, Tech Support]

I finally implemented most of Virginie's monthly totals into my sheet, using the Q column and a bunch of COUNTIFS and SUMIFS... glad I decided to figure it out myself, because in this case, Virginie's formulas with all their &s to create nice formatting are even scarier than my usual formulas...

I could still use a bit of a color re-scheming at some point, but it'll do for now... I have monthly tallies of books read, BRs done, points earned, and pages read (and graphic novels... but since I only did one graphic novel in 6 months, that seems like overkill)

[Rob]

Iain wrote: "I finally implemented most of Virginie's monthly totals into my sheet, using the Q column and a bunch of COUNTIFS and SUMIFS... glad I decided to figure it out myself, because in this case, Virgini..."

Hmmm. Might be fun to implement this on Julia's page. Not worth it on mine I don't think though :) Virginie's use of pastels is a little bit less eye-jarring, indeed.

Tempting to give everyone a slightly more elaborate page to start next year, but don't' want to slow the sheet down too much for those on slower connections ur using it via phone.

[Timelord Iain, Tech Support]

For now, I set each monthly tally to a different color to get a feel for them... there are plenty of darker color palletes to play with... it really is more for people with tons of books... not much need if you read less than 10 books per month, I don't think...

You should click on some of Virginie's formulas... they barely fit on my 40" TV monitor without going into multiple lines... especially the ones that tally Graphic Novels & Books read each month...

[Rob]

Iain wrote: "For now, I set each monthly tally to a different color to get a feel for them... there are plenty of darker color palletes to play with... it really is more for people with tons of books... not muc..."

I'll dig into it sometime. :) I can go hours down the rabbit-hole excel-geeking.

[Virginie]

Iain wrote: "I finally implemented most of Virginie's monthly totals into my sheet, using the Q column and a bunch of COUNTIFS and SUMIFS... glad I decided to figure it out myself, because in this case, Virgini..."

Yay, Excel geeking!
I had an automated monthly tracker where I just had to put the month number in a cell to get all the stats. I changed it to be able to see all monthly stats at once so a lot of my formulas are overkill now. I was just too lazy to rewrite ;)

[Virginie]

Rob wrote: "Iain wrote: "I finally implemented most of Virginie's monthly totals into my sheet, using the Q column and a bunch of COUNTIFS and SUMIFS... glad I decided to figure it out myself, because in this ..."

The changes I made to Zaara's, Veronica's and Melissa's were mostly cosmetics. I wanted to make the sheets easier on the eye since I'm doing most of their updates.

EDIT: Rob, I can quickly add monthly trackers to Julia's sheet if you want. Something similar to what I did on Veronica's scoresheet. :)

[Virginie]

Iain wrote: "You should click on some of Virginie's formulas... they barely fit on my 40" TV monitor without going into multiple lines... especially the ones that tally Graphic Novels & Books read each month... "

Yeah, I went a little crazy on those...

[Rob]

Virginie wrote: "Rob wrote: "Iain wrote: "I finally implemented most of Virginie's monthly totals into my sheet, using the Q column and a bunch of COUNTIFS and SUMIFS... glad I decided to figure it out myself, beca..."

Go for it :)

[Virginie]

All done! I took the liberty of changing border types (and other minor changes) to make it less aggressive to the eye ;)

As for the monthly header, all you have to do is copy the entire line after the last book and change to number in the formulas (7 for July, etc.). (don't know if I'm being clear, I'm tired :p)

EDIT: I screwed it up a little bit, but it's fixed now... ^_^"

[Rob]

Thanks for the time saver!

[Timelord Iain, Tech Support]

I think I figured out what you did now that I looked at it some more... but isn't January going to break in October/November/December, once you reach double digit months that all start with 1?... 10/11/12

[Virginie]

I didn't even think of that ^_^" I just checked and it's not a problem. Everything works as intended :D

Books are added to the monthly tally only if the value in the (hidden) column Q, which is the month number, is exactly equal to the value in cell C166 (for the June tracker, for example).

I only had one row like this previously, and I was checking my monthly stats simply by changing the number to the month I was currently interested in (in cell C something). For example, if you change cell C166 from 6 to 7, you're tracking the July tally.

EDIT: Iain, I'm thinking you'd be better off checking the formulas in Veronica's spreadsheet. Mine are unnecessarily complicated because I read a lot of mangas, wanted to track my DNF, etc.

[Timelord Iain, Tech Support]

Virginie wrote: "I didn't even think of that ^_^" I just checked and it's not a problem. Everything works as intended :D

Books are added to the monthly tally only if the value in the (hidden) column Q, which is th..."

Thanks... I went back and re-did all my formulas this morning to take advantage of a Month / Tally column... this allowed me to go back and make the monthly totals formulas modular and copy/pasteable... and I resimplified the top totals back to single ifs instead of multiple, since I'm just adding the monthly totals together...

I guess I'll have to make further changes if I ever decide to add labels to my monthly totals... probably =SUMIF(q4:q400, ">0", j4:j400)

I'm done fiddling for today tho...

[Virginie]

Iain wrote: "I resimplified the top totals back to single ifs instead of multiple, since I'm just adding the monthly totals together..."

Oh, good idea! I just took a look at it, it looks great :)

I could be wrong, but are you manually entering the month number in column Q? You could use the MONTH function to do it automatically:
=IF(C4="","",MONTH(C4))
With that, your Q column will only fill if you've added a read date. That way, you can further simplify your monthly totals formulas: instead of having two conditions (B4:B400, "<>", Q4:Q400, "6"), you can simply have one (Q:Q, "6")

[Timelord Iain, Tech Support]

I dunno if it was my sheet shenanigans, but the Blood Eagles formula stopped working, same way Tome Raiders broke a few months ago... I re-created our list in column I to get it working again, short-term... how did you guys fix it last time?

[Timelord Iain, Tech Support]

Virginie wrote: "Iain wrote: "I resimplified the top totals back to single ifs instead of multiple, since I'm just adding the monthly totals together..."

Oh, good idea! I just took a look at it, it looks great :)
..."

Thanks... I'll implement that later... I filled the month column in, this morning, by writing it once and dragging the cell to copy it 30+ times in each month...

Do you just not have those formulas in your monthly tally rows?... Cause I use the date read month in that row to tally books read that month...

[Virginie]

Iain wrote: "I dunno if it was my sheet shenanigans, but the Blood Eagles formula stopped working, same way Tome Raiders broke a few months ago... I re-created our list in column I to get it working again, shor..."

Eh, I have no idea how it got fixed (or by whom) but... That means that I broke the Tome Raiders formula the first time, right?
Oops... sorry :p

[Virginie]

Iain wrote: "Do you just not have those formulas in your monthly tally rows?... Cause I use the date read month in that row to tally books read that month... "

I'm not sure what you mean... What I did for my spreadsheet (and some others :p) is that I used the MONTH formula I gave you earlier, dragged it to the entire Q column and never touched those cells again. I only have to entire read date (column B) and all the monthly stats update. Does that make sense?

[Timelord Iain, Tech Support]

What happens if in your January row, it says "30 books read" in column B?

[Virginie]

Oh, I get it now (at least, I think I do). Nope, I don't have those formulas in the Q column for my monthly rows. When creating a new monthly row, I make sure to copy an entire line so I don't have problems with formulas in hidden columns.

I was "cheating" in order not to get my monthly stats included in the annual totals: when you merge cells, only the top left one is considered filled, the others are empty. But I like your summing of the monthly tallies better, and will implement that soon :)

[Timelord Iain, Tech Support]

Virginie wrote: "Oh, I get it now (at least, I think I do). Nope, I don't have those formulas in the Q column for my monthly rows. When creating a new monthly row, I make sure to copy an entire line so I don't have..."

Yea, that's what I did, as well... you might have to do something more complicated to parse out your plain text for adding monthly tallies together...

[Virginie]

Oh yeah. I meant I'll implement that into the other sheets I'm updating (just did it on Zaara's). I'm too scared of the formulas in my own scoresheet --"

EDIT: Actually, I made partial totals to differentiate GN and 'normal' books. With the IF conditions, the monthly tallies won't be included in those. I just have to sum the 2 :o I don't even know why I didn't do that to begin with ><

EDIT 2: Yeah, still too lazy to modify my own sheet. It'll stay that way

EDIT 3: Zaara's sheet has all the newest implements, if you want to take a look :) Column I for the monthly rows is the month number. Every month, I just have to copy the row and change that cell and voilà! (I made it so that even the month's name changes ;) )

[Timelord Iain, Tech Support]

Nice... you cut out ALL the hard values now... I didn't know there was a formula to write the month name... is there one to determine the number of days in a month?... 30 days hath September, and all that... for doing monthly averages...

Also... why do some formulas need "$K:$K" and others just use "K:K"?... I really should have simplified some of my formulas to that, since I'm already upping them to j4-j400 since I read so much that I need more than 300 lines...

[Timelord Iain, Tech Support]

Virginie wrote: "Oh yeah. I meant I'll implement that into the other sheets I'm updating (just did it on Zaara's). I'm too scared of the formulas in my own scoresheet --"

EDIT: Actually, I made partial totals to d..."

Also... I put off updating my sheet all the time, because it's a lot of work, and I need to make sure I don't screw anything up and gain/lose points... time better spent reading...

[Charlie]

Hey all, you might know this as I haven't read through all the comments but just a heads up, the spreadsheet looks a little broken; Tome Raiders is showing as having 0 points on the first page.