Fantasy Buddy Reads discussion
Archive [General]
>
2021 Team Competition Tracker/Google Sheet Central
Thanks, Iain! The sheets are a lot of hard work, but if the basics are allready there, then we can start ;)
Mod
Hopefully, eventually, they won't really be any work at all... many of the changes I made this year mean that we could start a 2022 competition right now with a year change in a single location, and team changes on the Participant Data sheet...
I actually think most of the team coloring could be moved to conditional formatting, especially in the Simple/Monthly templates... altho it would require a bit more help/know-how for players to change the colorings after, or when adding rows/columns...
If i do that over the next little while, 2022+ will be almost no work at all... the goal, really...
I think I spent 20-30hrs on the sheet this year, and most of that was me doing things the slow way, because I worry about screwing everything up making vast changes with the Find & Replace functions... Virginie was more comfortable doing that... altho I DID use it to fix the Point Difference bug I found a little bit ago...
Hey, I got an error message for Comeback - Date Read: It has - 1/1/21
Error Message -
Invalid:
Input must be between 1/1/2020 and 12/31/2020
--- I tried using dragging to fix it. Original message was 1/1/2019 and 12/31/2019.
Hm, my pages has bunch of errors. No idea what's going on. Tallies for Page Points/BR Bonus are #N/A.
Thanks for all the work on the spreadsheets Iain!! I was changing the colours to get rid of the blue on my sheet but some cells wouldn't change and I think I messed something up on the formatting in the L17 cell when trying to change that block to purple. When you have a second could you just check. I left trying to change the colours on the rest so I didn't mess anything else up to 😅
Mod
I fixed your error... proved easier than whatever is going on with Anni's sheet, which I worry is gonna spring up in other sheets...
Yours was with the audiobook cells... you don't need to type " hrs"... I actually faked that in the spreadsheet in the Format -> Number dropdown... it formats all numbers in that column to end in " hrs" so it can have a descriptor, and still be easily added together using the SUM(formula)... so just type 5.25 and it'll read 5.25 hrs and the points formulas won't break...
Mod
I was changing the colours to get rid of the blue on my sheet but some cells wouldn't change and I think I messed something up on the formatting..."
Any colors that won't change are being overridden by Conditional Formatting... highlight the cell, right click and go to Conditional Formatting at the bottom... see what's formatting it, and either remove those cells from the range, or change the color there to change the relevant section to the color... if that doesn't resolve, I can go in and make changes...
I saw the colour changes you made Iain and that is perfect thank you!!
Mod
I changed the borders to the same green as the conditional formatting, so they're invisible when everything is 0s, but show up when you have numbers everywhere...
Also just changed your alternating colors for the book section... but left an explanation how to change again or change back to light blue 3 in your book section...
Timelord Iain wrote: "Hailee wrote: "I saw the colour changes you made Iain and that is perfect thank you!!"I changed the borders to the same green as the conditional formatting, so they're invisible when everything i..."
That is brilliant Iain. I wanted to change the alternating colours in the book section to a shade of green but didn't know how. The one you picked is perfect so I'll definitely keep that one.
Now I just have one sparkline that I need to change from blue. I'm going to have dinner now so I'll have a look at it in bit.
Thanks for your help Iain. I really appreciate it. Hope you are having a good New Years Day
Mod
I changed the borders to the same green as the conditional formatting, so they're invis..."
the sparklines are harder... you have to input everything in hex... you can get a hex color editor from the bottom of the color options in font/background... gives you a color circle to play with... but it's really hard to decide on the right combo without experimentation... when you get a color you like, or want to try, copy the #2f3e4a hex given at the top of the box and try it out on the sparkline with some fake sample books to get an idea... good luck...
Most of my color decisions came from going back and checking the 2020/2019 sheets...
And my New Year's is going great... except spreadsheets keep distracting me from reading :D :angryface:
Ahh good to know it wasn't changing for a legitimate reason rather than my stupidity.Haha I'm not surprised you haven't got much reading done what with having to put out everyone's spreadsheet fires lol. I haven't got much reading done either, although I don't have a legitimate reason besides laziness.
Iain, is there an easy way to modify the Genre list? I tried targeted clicking to look around but that was kind of dumb of me. Ha. So, next will be to look online how to find hidden lists and alter them.
Mod
So, next will be to look online how to find hidden lists and alt..."
Hidden rows/columns have little arrows next to them to unhide... in this case, it's column U, in the top 15 row area that's collapsed...
I took advantage of the extra November/December tally, and expanded the area... and went through and updated the dropdowns and pie chart, to support U3:U15...
So you can pick 13 genres of your choice... just place them in cells U3 to U15, and they'll populate the dropdown menus in column G, and fill in the pie chart in the near upper right, with the help of tallies in T3:T15...
Warning: the way I setup the chart, it doesn't count re-reads, since I figured it would skew my 2020 reads heavily towards UF... if you want to include re-reads in your pie chart, you need to remove the section of the formula that checks if column A = FALSE from the formulas in T3:T15... or I can do it... and if you want more than 13 genres, we'll probably have to move the whole thing over to VW17+... I don't think there's any data there...
My sheet has so many columns, I barely remember all the bells & whistles, anymore...
LoL - I redid the genres. Left a few open in case I am forgetting something. I was going to put Cultivation in but I'd only use it for two series right now. Thanks!!! =D
Mod
Thanks!!! =D"
That's the challenge... including genres with enough books to warrant, and coming up with distinctions in the fantasy genre that aren't too wordy...
I just signed up a few minutes ago, and thought I'd ask for the same sheet i had last year please.I would like to add a new read/reread tracker and a book/e-book/audiobook tracker in the same style as the short stories/novellas/books/tomes if possible :) and of course trackers for november and december as well.
Thanks so much in advance! :D
Oh and you can delete the non-fiction/fiction tracker, I don't really need it after all
The book/e-book/audiobook tracker sounds really interesting. Could I possibly get that too?
Mod
For the book/e-book/audiobook tracker instead of genre, you can follow Iain's directions that he gave me for changing the genre list. Otherwise, he will need to setup a new menu & put it somewhere on the "dark (hidden) side". =)Assuming you guys have the fancy sheet. That's the one I asked for this year. (aka Iain/Virginie sheet)
Hidden rows/columns have little arrows next to them to unhide... in this case, it's column U, in the top 15 row area that's collapsed...
I took advantage of the extra November/December tally, and expanded the area... and went through and updated the dropdowns and pie chart, to support U3:U15...
So you can pick 13 genres of your choice... just place them in cells U3 to U15, and they'll populate the dropdown menus in column G, and fill in the pie chart in the near upper right, with the help of tallies in T3:T15...
Warning: the way I setup the chart, it doesn't count re-reads, since I figured it would skew my 2020 reads heavily towards UF... if you want to include re-reads in your pie chart, you need to remove the section of the formula that checks if column A = FALSE from the formulas in T3:T15... or I can do it... and if you want more than 13 genres, we'll probably have to move the whole thing over to VW17+... I don't think there's any data there...
It's totally handy to be able to adjust the genres myself. I added Zombies. Debating calling it the Walking Dead, Shamblers, Evil Dead, etc. I'll probably have fun with it. lol
Thanks so much! The sheets look great :D Can't wait to start~
I also tried fiddling with the colors, but I noticed the conditional formatting on certain things, which I'm less confident about changing. As long as the colors don't clash too badly once I start inputting books and the graphs come in, I'm alright. Experimentally, I tried to put in 2 different genres, but my circle graph only registered 1.
Also... I realized I may have been going about the row colors wrong, changing them line by line. In my previous sheet, even if I slid a row up or down, the colors would automatically alternate, but the way I did it, if I move anything around, there's the potential for a block of color. Is there another way to do it?
Melissa, you highlight the rows you want to change the colors, then click on Format => Alternating Colors => Set colors.I think the graph changes once the book has a "Read/Finished Date".
Like, I read 4 books and my graph is divided in quarters. =)
Mod
There's a hidden column that parses out the month from the date read column, and uses that to generate the monthly totals at the top, and as a true/false test for points/etc...
Mod
Soo wrote: "Melissa, you highlight the rows you want to change the colors, then click on Format => Alternating Colors => Set colors.I think the graph changes once the book has a "Read/Finished Date".
Like, ..."
Thanks Soo. I tried that, and it did work to batch edit all the rows. But if I move things around the colors still stick. It seems like when I changed the colors by row using fill color, those "stuck" and are overriding the alternating color formatting? I'm not sure how to undo that though.
I had the same problem. The changes happen & stay but it doesn't look like it because the page doesn't "refresh" to show it. When I went back to the my tracker later, it had the alternating colors that I wanted.
Mod
Mod
Mod
Timelord Iain wrote: "Uh... no it didn’t... I fixed it when I was fiddling"Hahahaha! Iain, fiddling and cleaning up behind the scenes.
It reminds me of this Magic Coffee Table sketch.
Ah okay, I spotted the Reset button at the top of the Alternating Colors menu, and that fixed it.
Mod
Just wanted to know what constitutes a novella, book, tome?I know a tome is 500+ pages, just wondering how many pages the other two usually are..
Timelord Iain wrote: "Makes sense... when I fiddled with Soo’s sheet, I just knew that turning off alternate colors and thrning them back on didn’t fix it..."Then I had to go back and re-color stuff. BUT the rest is fixed. Thanks! I didn't know about the reset button. Gotta remember that.
Mod
I know a tome is 500+ pages, just wondering how many pages the other two usually are.."
If I remember correctly, the default breakpoints are 50 pages, 250 pages, and 600 pages, for shorts to novellas to novels to tomes... you can change the breakpoints in the formulas...
On most sheets, the only place this is tracked is in the graph, and the numbers used in the graph are hidden in cells A5, B5, A14, and B14... you can't see the numbers because the font color matches the background color...
If you decide to change the formulas, it's the column E section of the formula in those cells you want to change... like short stories are <51, novellas are >50 && <251... that way 50 page books are only counted in 1 tally... if you used <50 and >50, 50 page books wouldn't be counted in either...
Mod
Then I had to go back and re-color st..."
Now that I looked at the sheet, I think reset worked because it set the background back to white, the default, and alternating colors was still active and overwrites the default...
This sheet is so fancy that I'm leery of doing too much to it. The problems will arise when I add rows. Making sure to highlight+drag to make sure the formulas are in place. I'm happy I can track so many things on it and unhappy that it takes more work to keep it functional. lol
Get over it brain! It's just another series of habit checks to rewrite.
@Iain, Woahhh! Hey, I ain't changing nothing! Just wanted to know which is which lol. So up to 600 pages counts as a normal book... interesting..
Mod
From what I remember, I did a fair amount of google research when creating the breakpoints, but in the end, alot of it is subjective...
I've read a fair amount of 50-100 page stories struggling to be novella PNRs, and a fair amount of 275ish page books struggling to reach the 300 page threshold... and almost all the Dresden Files books are 532 pages, but all the Wheel of Times books are at least 600 pages, and I consider them tomes... so 50/250/600 was what I ended up on...
Then Virginie copied the graph to all sheets, the next year...
Mod
I noticed although dates are filled in, points/pages aren't registering on the main page for some of us:Melissa
Paula
Elizabeth
They seem to be calculated in the righthand column fine at least.
Mod
Elizabeth wrote: "If I can, I'd really like the month divide that Soo has."
Done... I literally copied Soo's month dividers and grouped 20-30 rows into each month... the blue color will match better if/when your sheet is blue to match your team this year...
For the Ebook/Physical Book/Audiobook chart, I added an E-book checkbox... that will differentiate between ebooks and physical books... audiobooks are determined based on whether the audio column is greater than 0%...
If you want to count books in multiple formats, when you split between audio and e/phys, I'll need to change the formula, let me know...
Mod
Melissa
Paula
Elizabeth
They seem to be calculated in the righthand column fine at least."
Sounds like a bug with people that had their sheets duplicated from somebody else's sheet this year... I'll look into it...
Mod
Melissa
Paula
Elizabeth
They seem to be calculated in the righthand column fine at least."
When you said Main Page, I take it you meant the Participant Data page, Ranking page, or the top row of the player page...
Either way, turns out it was 3 different problems...
Elizabeth: I was working on making changes to her sheet, and temporarily broke some formulas because I was adding columns...
Paula: I broke this several days ago, making changes to her and Anni's sheet, to be more efficient... when I copied the changes from Anni's sheet, I forgot to add the new helper column Q that was needed to make everything work...
You: somehow, all your month formulas didn't start tracking until row 23... did you add 5 rows to your sheet, near the top... either way, it was weird...
Timelord Iain wrote: "somehow, all your month formulas didn't start tracking until row 23... did you add 5 rows to your sheet, near the top... either way, it was weird..."Thanks Iain. Those first rows were where I initially fiddled with filling alternating colors, so I must've broken something in the process of doing that.
Mod
Thanks Iain. Those first..."
Did someone else go in and give you all the requested changes you wanted?... Re-read checkbox & graph, etc... cause I didn't do it, and I see it all there already, and corrections to Participant Data cells needed to make everything work...
Not that I'm complaining... less work for me :D
No, I think I simply asked to copy Sha's sheet from last year, and that's what I got.Question: How difficult would it be to add additional columns of checkboxes? It doesn't need to correspond to any fancy graph, but it'd be nice to tally Nonmale and BIPOC authors for diversity challenges. Could it be done in the space to the right without messing up formulas? If it needs to be in the central portion of the sheet, I could take out the audio column, since I never use audio.
I know you've put a lot of work into the sheets, so if you need to say Later or No that's too much work, those are also valid responses. Take care of yourself Iain. :)
While wiping fake points and setting up the official teams, I realized there's a problem with the "Points Difference to #1" formula that I need to sort out...
And I still need to meet Elizabeth and Paula's customization requests...
And there's a lot of work left to recolor everyone's sheets to Team Colors...
But, everything is ready to track the main competition, so it's functional...
Enjoy!