2019 Team Competition Tracker/Google Sheet Central

[Timelord Iain, Tech Support]

That's the whole point of this thread... sharing cool stats/formulas :)

[Kathrine]

Timelord Iain (Eeyawn) wrote: "That's the whole point of this thread... sharing cool stats/formulas :)"

I'm so happy to be learning new things in the wondrous world of spreadsheets!

And I'm also really excited for the competition to begin so I can start reading and filling in my sheet :D

[Virginie]

Iain is right, making improvements and sharing the newest formulas is what this thread is for...

(yay Excel nerds!)

[Angela Jr.]

Kathrine wrote: "Timelord Iain (Eeyawn) wrote: "That's the whole point of this thread... sharing cool stats/formulas :)"

I'm so happy to be learning new things in the wondrous world of spreadsheets!

And I'm also..."

I'm also excited to start filling in my sheet! I have no idea how to do any formulas in excel, but i love lists and stats 😃

[Frank]

@Scott and Nikki

Just checking in.
It has been three weeks since we exchanged messages regarding my error in not signing up, wondering since we are approaching Jan. 1, if I can get on a team?

Thanks again.

[Narilka]

Frank you might want to send them a direct message just in case they miss this post.

[Angela, I'm Silverfox lost]

Sorry for not getting back to you sooner Frank. You are on Niki's team. 😀

[Brian]

Can one of the spreadsheet geniuses look at mine and tell me if I screwed something up. The book points and challenge points aren't showing up when I enter the stuff like on others people's sheets? Not sure if I did something

[Frank]

Angela wrote: "Sorry for not getting back to you sooner Frank. You are on Niki's team. 😀"

Thank you Angela, I will miss the Jaghut's!

[Scott , Karsa Orlong]

Can you guys add Frank as a tab to team Niki? Much appreciated.

[Karishma]

Timelord Iain (Eeyawn) wrote: "Saar The Book owl wrote: "Timelord Iain (Eeyawn) wrote: "Saar The Book owl wrote: "Okay, thanks, Iain :)"

I edited my previous comment with ideas... if you want to track how many doorstopper tomes..."

Could you do that for me too - your 3 graphs along with leaving space for adding challenge links to the side?

[Karishma]

Virginie is it possible to add a column after Series and change the place of date and page no like in your sheet - so 3 columns - Series - Book No - Pages.

Edit - And maybe a genre column?

[Virginie]

Yup to everything. I'm coming back home tomorrow evening, and will get to work on all the requests after that unless someone (hum, hum Iain) get to it before :)

[Timelord Iain, Tech Support]

Scott wrote: "Can you guys add Frank as a tab to team Niki? Much appreciated."

Frank wrote: "Angela wrote: "Sorry for not getting back to you sooner Frank. You are on Niki's team. 😀"

Thank you Angela, I will miss the Jaghut's!"

Before we do that, have you looked at the sheet, yet, Frank?... Do you have a preference for what info you want in the top area?

There's the template, which most people have, but then me and Virginie started personalizing our sheets with graphs & more detailed breakdowns (by book size / etc)... and other people liked it and requested it on their sheet... you can look at the sheets of Jenna / Saar / Kathrine / Rob see the simplified middle-ground... and Manju / Yanique have a scatterplot of their books by publication year, which required an extra data column...

So many ideas to choose from, and if you choose before we make your tab, we can just duplicate that sheet and put your name on it, instead of having to copy in all the formula changes later...

[Karishma]

Timelord Iain (Eeyawn) wrote: "Scott wrote: "Can you guys add Frank as a tab to team Niki? Much appreciated."

Frank wrote: "Angela wrote: "Sorry for not getting back to you sooner Frank. You are on Niki's team. 😀"

Thank you An..."

After seeing Rob's sheet - could you move the Book Title etc Row to just above where the book listings start and make it filterable?

[Timelord Iain, Tech Support]

Brian wrote: "Can one of the spreadsheet geniuses look at mine and tell me if I screwed something up. The book points and challenge points aren't showing up when I enter the stuff like on others people's sheets?..."

Your font color was green... formula results were just invisible... we have a conditional format on those columns that makes the font match the background when the result is 0.0, but the font needs to be white (or another visible color) by default to be visible when the number is greater than 0.0...

I set the font to white for all 300 rows, just to be sure... all sorted...

[Timelord Iain, Tech Support]

Virginie wrote: "Yup to everything. I'm coming back home tomorrow evening, and will get to work on all the requests after that unless someone (hum, hum Iain) get to it before :)"

I just copied my entire frozen area over into Karishma's sheet, since she wanted everything I had, but I don't know an easy way to add columns without screwing the layout... any help you could give to add 2-3 columns without borking everything, would be great :)

Unless I missed one, I've helped with all the requests while you've been away... Saar, Kathrine, Karishma, etc...

[Karishma]

Timelord Iain (Eeyawn) wrote: "Virginie wrote: "Yup to everything. I'm coming back home tomorrow evening, and will get to work on all the requests after that unless someone (hum, hum Iain) get to it before :)"

I just copied my ..."

Many thanks to you Iain!!

[Timelord Iain, Tech Support]

Karishma wrote: "Timelord Iain (Eeyawn) wrote: "Scott wrote: "Can you guys add Frank as a tab to team Niki? Much appreciated."

Frank wrote: "Angela wrote: "Sorry for not getting back to you sooner Frank. You are o..."

Just noticed this... I made that move on instinct, since it makes more sense once you start matching the monthly tallies to the top row totals... but Virginie is the one with filter view experience, so I'll let her help there...

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

Could I request an additional sheet on team Tome raiders for Frank? Thank you :)

[Rob]

Karishma wrote: "Okay so Virginie - what is left is

1. Making the book titles etc filterable
2. Adding and rearranging columns so that - series - book no - pages - genre and then you can add date wherever you want..."

Added the filter Karishma. what order did you want things in?

Think i got it all calculating correctly and in the right order too now.

[Rob]

Rob wrote: "Karishma wrote: "Okay so Virginie - what is left is

1. Making the book titles etc filterable
2. Adding and rearranging columns so that - series - book no - pages - genre and then you can add date ..."

The shifting of rows may not let you use the google form though.

[Karishma]

Rob wrote: "Rob wrote: "Karishma wrote: "Okay so Virginie - what is left is

1. Making the book titles etc filterable
2. Adding and rearranging columns so that - series - book no - pages - genre and then you c..."

Hi Rob, thanks for adding the filter, I just want the date and pages column to be interchanged. I will not be using the form so not a problem.

[Virginie]

Thanks for picking up my slack, fellow Excel nerds :)

[Timelord Iain, Tech Support]

Rob wrote: "Rob wrote: "Karishma wrote: "Okay so Virginie - what is left is

1. Making the book titles etc filterable
2. Adding and rearranging columns so that - series - book no - pages - genre and then you c..."

Since we're not inputting the Form Entries into the personalized sheets, there's no correlation... all Form Calculations are done separately, and added into the top row totals only...

[Rob]

Timelord Iain (Eeyawn) wrote: "Rob wrote: "Rob wrote: "Karishma wrote: "Okay so Virginie - what is left is

1. Making the book titles etc filterable
2. Adding and rearranging columns so that - series - book no - pages - genre an..."

Duh :) My mind went back to earlier versions for some strange reason.

[Angela, I'm Silverfox lost]

Frank we're going to miss you on our team as well.

[Timelord Iain, Tech Support]

I stole the series # column from Karishma's sheet already... only change I made, was right aligning the series column so the series name leads right into the number in the adjoining column...

Also stole the genre column, once implemented, altho I still need to go in and adjust the genre choices for my tastes...

[Karishma]

Timelord Iain (Eeyawn) wrote: "I stole the series # column from Karishma's sheet already... only change I made, was right aligning the series column so the series name leads right into the number in the adjoining column...

Also..."

Yes, I have kept that whole column to enter genre choices - those were test choices to see if my formulas and adding the column did not cause any problems. So I can add genres as and when required, so I got the statistic I wanted XD

Although for some reason adding the genre column messed up my monthly totals for novellas/standalones etc formulas - have done a strike through for the formulas still to be corrected.

My laptop charger broke down yesterday night and couldn't get one today because stupid Sunday.

[Timelord Iain, Tech Support]

Now that I have a # column for series numbering, I think I'm going to change my Standalones / Series graph to Standalones / New Series / Continuing Series... it will measure based on "/" for Standalones, "1" for new series, and then everything else is continuing...

[Timelord Iain, Tech Support]

Karishma wrote: "Timelord Iain (Eeyawn) wrote: "I stole the series # column from Karishma's sheet already... only change I made, was right aligning the series column so the series name leads right into the number i..."

At a cursory glance all your formulas seem right to me... did someone go in and fix them already?... the main problem with formulas is sometimes when you are shifting around columns and adding columns/rows, the formulas shift relative to their old target in ways you don't want... it's what makes the idea of swapping the date column and page column so daunting... and moddinig sheets in general with different amounts of columns...

but, now that I think about it, I don't think swapping 2 columns would be too hard... we'd just need to fix the monthly rows again once done, otherwise it would go Novellas | Shorts | Books | Tomes...

[Anni]

Timelord Iain (Eeyawn) wrote: "Now that I have a # column for series numbering, I think I'm going to change my Standalones / Series graph to Standalones / New Series / Continuing Series... it will measure based on "/" for Standa..."

You could add a star or something behind the series number, if it's the last book in the series and add a "Finished Series“ category :)

[Timelord Iain, Tech Support]

I think the easiest way to rearrange the columns would require 2 duplicate tabs...

1) Duplicate your tab
2) rearrange columns into desired order (assuming Sheets fixes the formulas)
3) Duplicate your tab again
4a) Use your first duplicate tab to copy over the layout of rows 1-15 in the correct order...
4b) Delete 1st duplicate tab
5a) Copy over formulas (one column at a time) from the 2nd duplicate, to get the right $H$16:$H references in your row 1-15 formulas
5b) Delete your 2nd duplicate tab
DONE

[Karishma]

Timelord Iain (Eeyawn) wrote: "Karishma wrote: "Timelord Iain (Eeyawn) wrote: "I stole the series # column from Karishma's sheet already... only change I made, was right aligning the series column so the series name leads right ..."

That is what happened - the formulas were for date column e which changed to g after inserting the genre row. - when I left it yesterday all that was needed was to change the month nos 1,2,3,4,5,6 etc, while copying them they were all 1.

I changed the parameters to suit my ideas of lengths for shorts (75 pages) novellas (175) , books (400) etc.

[Karishma]

Timelord Iain (Eeyawn) wrote: "Karishma wrote: "Timelord Iain (Eeyawn) wrote: "I stole the series # column from Karishma's sheet already... only change I made, was right aligning the series column so the series name leads right ..."

Let's see if Virginie can do it easily, if not it's not a big deal, I'm very happy with the other changes.

[Timelord Iain, Tech Support]

Sir Anni wrote: "Timelord Iain (Eeyawn) wrote: "Now that I have a # column for series numbering, I think I'm going to change my Standalones / Series graph to Standalones / New Series / Continuing Series... it will ..."

I was trying to come up with something like that... just need to figure out if countif can do a partial match...

Google answered me: =countif(regexmatch(D16:D, "★")) then, to make inserting stars easier, I can set a data validation for the column with the ★ as the only option, but not make the dropdown menu mandatory, and any time I finish a series I can add the star with 2 clicks, and just type a number after: ★3

EDIT: Data Validation is a bad idea... invalid inputs aren't allowed and get deleted... so I'll just have to store a star on my sheet for easy copy/pase...

EDIT2: there's an easier way for countifs... it supports wildcards... so I can just do ★* and it will find any field that starts with a star...

[Soo]

I keep looking at my page & wondering if I want those. I don't need them though.

These talks just make me want to go work on the old excel sheet I already have and fiddle with it. LOL

Plus the downside of having a pull down menu for genres is that some books are more than one.

See? Example of only a few things my brain rambles about.

This is why I found a book app that does all this for me & it's customizable + free. Also syncs with GR (if I want it to). Options & tidy.

[Timelord Iain, Tech Support]

Soo wrote: "I keep looking at my page & wondering if I want those. I don't need them though.

These talks just make me want to go work on the old excel sheet I already have and fiddle with it. LOL

Plus the ..."

I generally just try to pick a single genre for things... stick to the umbrella genres like Urban Fantasy and Steampunk, instead of the niche subgenres, like Dieselpunk, Aetherpunk, Solarpunk, Weird Western, etc...

[Soo]

I try to use the main genres & a few of the subs. If I want a particular note about Spaghetti Western or something like that, I would put it in notes.

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

Since I can’t open the file on my phone for whatever reason, I thought I would just ask to check if frank’s page has been added haha. Are we good to go? Thanks again for all your amazing excel work.

[Timelord Iain, Tech Support]

Niki Hawkes wrote: "Since I can’t open the file on my phone for whatever reason, I thought I would just ask to check if frank’s page has been added haha. Are we good to go? Thanks again for all your amazing excel work."

I was trying to wait for a response from him on whether he wanted the default sheet or a modified one, but I went ahead and set up a default sheet... he has a sheet, and he's in the Participant Data sheet for tallies... not sure what all else I need to do to insert him into the competition... Virginie should be around later today/tomorrow to fix anything I missed (such as not knowing how to setup the Form filterview everybody else has)

EDIT: this was the first time I added someone to the competition, and even tho I don't know everything I needed to do to set it up, I love how much we automated... once I put Frank into the Participant Data sheet, all his formulas started working, he appeared in all the ranking tabs... all that's missing is the Form integration and some hyperlinks, I think... I also made sure to add his name to the name list in the Form...

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

Thank you!

[Rob]

I'm wondering if it would be a good idea if we made sure everyone tested their sheet to make sure they've even looked at them yet and then also to see with every sheet having at least one record populated, verifying the whole thing works.

[Siobhan]

Rob wrote: "I'm wondering if it would be a good idea if we made sure everyone tested their sheet to make sure they've even looked at them yet and then also to see with every sheet having at least one record po..."

Good idea Rob. I put a date in for a book yesterday just to test it and it didn't add my points to the first/main page leader board. It did add them to the second page leader boards. I just figured you guys hadn't made it live yet.

[Siobhan]

Just tried it again. The Participants page updated straight away, but the Main Page and Rankings seem to be delayed? They didn't update when I filled one date in, but then did when I put two dates in. Then when I removed a date, they still say I have 30 points instead of 20. (Obviously I'll take the date out, just testing! :D)

[Frank]

Timelord Iain (Eeyawn) wrote: "Niki Hawkes wrote: "Since I can’t open the file on my phone for whatever reason, I thought I would just ask to check if frank’s page has been added haha. Are we good to go? Thanks again for all you..."
@Iain
I actually had sent a message to Niki on the Excel sheet, and had some questions on the new formats since I am a bit late to the game for this year.
I was wondering can you modify, as we did last year for our own ease of reading on the eyes?
I noticed some of the tabs were modified quite nice, but do not know how they work internally and with the master tallies, such as Virgine's, Niki's, Manju's and Yanique's.

[Timelord Iain, Tech Support]

Rob wrote: "I'm wondering if it would be a good idea if we made sure everyone tested their sheet to make sure they've even looked at them yet and then also to see with every sheet having at least one record po..."

At this point, I'm resigned to a trial by fire once people start updating their sheets tomorrow... we'll iron out the kinks over the next week, as they arise...

[Timelord Iain, Tech Support]

Siobhan wrote: "Rob wrote: "I'm wondering if it would be a good idea if we made sure everyone tested their sheet to make sure they've even looked at them yet and then also to see with every sheet having at least o..."

There are a lot more moving parts this year... with sheets calling the Participant Data, then Participant Data calling the sheets, and the Form / etc... so the calculations might move a bit slower, but relatively speaking, we don't need them to update often... the personal page tallies tend to update quickly, and they appear on the other pages shortly... we'll see how it fairs, once there are several hundred/thousand book entries...

[Timelord Iain, Tech Support]

Frank wrote: "Timelord Iain (Eeyawn) wrote: "Niki Hawkes wrote: "Since I can’t open the file on my phone for whatever reason, I thought I would just ask to check if frank’s page has been added haha. Are we good ..."

Colors have no effect on the formulas... adjust those to your heart's content... shifting columns around tends to be fine, since the Sheets will adjust the formulas... same with adding extra columns for new data you want to track...

The only time we NEED to enter the fray, is when you change the layout of the top 2 rows, because then we need to adjust the data grabs in the Participant Data tab... everything else grabs from there for simplicity...

[Timelord Iain, Tech Support]

Timelord Iain (Eeyawn) wrote: "I think the easiest way to rearrange the columns would require 2 duplicate tabs...

1) Duplicate your tab
2) rearrange columns into desired order (assuming Sheets fixes the formulas)
3) Duplicate y..."

NOPE... Much easier way:

1) Highlight the majority of the top 15 rows (A1 thru the last row before the Book / Series / Author headers, and the point column before the big square graph area...)
2) CTRL-C
3) rearrange any rows you want to rearrange
4) Highlight cell A1
5) CTRL-V

Much easier... clearly my brain is dulled from flu meds :)
This is basically what I did copying my whole top area over to Karishma's sheet... then put her name back in cell A1 (I only copied through to the points column, because screwing with the #gid data in the hidden O column by highlighting the rows would have screwed things up)