Calling all Spreadsheeters - ATY Community Spreadsheet

[Emily, Conterminous Mod]

Sorry Maggie, I just saw this message. I'm pretty good at it for the regular challenge tabs, but I will try to keep that in mind to check for the seasonal challenges as well!

[Emily, Conterminous Mod]

I need help from the spreadsheet hivemind because my brain is not working this morning. I want to make it so that the Summer Reading Challenge on the Community Spreadsheet calculates points, not total books read. Is there a formula I could use that will allow me to assign certain number of points per book completed, then total?

I'm thinking I could tally the points for each category, then use the bottom of the column to total the categories, but I can't remember/figure out how to make a book title = a certain number of points.

[Steve]

Emily, can you give a couple of examples of what kind of scoring system you’re looking for? Like what earns you points and how many do you earn? (I can probably play around at nap time today since I’m off today).

[Emily, Conterminous Mod]

I've put all the prompts on the community spreadsheet, if you want to take a look there. Basically the first five prompts are worth 100 points, the second worth 200, etc. People can read any prompts they want (once each) to accrue 5000 points.

I was thinking of putting some tally system on each prompt heading (shaded in light blue on the spreadsheet) that tallies how much the person has read from that set of prompts, then add each heading together at the bottom. I can get the summing of the headings right, but less sure about how to make a book entered = a certain number of points.

[Steve]

Emily wrote: "I've put all the prompts on the community spreadsheet, if you want to take a look there. Basically the first five prompts are worth 100 points, the second worth 200, etc. People can read any prompt..."

Oh, it's actually easier than I tried to make it initially... I spent 5 minutes trying one way and then had the "oh, wait a minute..." realization and figured it out in 45 seconds after that.

Here's the basic structure:
=counta(D5:D9)*100

Then for each section change the range and change the point value. At the bottom of the sheet, just add the totals in the section headers. (I've left my test in the S column for the moment if you want to see it/copy it. Feel fre to clear it whenever).

[Emily, Conterminous Mod]

Excellent. Thank you so much Steve, that's exactly what I was hoping for!

[Lindsay]

Hi everyone, I just want to thank Emily and Steve for all their work on the spreadsheets. They are one of my most favourite parts of this group, and it makes me sad that I’m in another challenge group as well as this one and they don’t have a spreadsheet!!! Thanks again xxx

[Barbara]

Man, I love spreadsheets! And I love you guys for coming up with it 😂

[LeahS]

I'll second or third that - particularly the Summer Challenge one, which is beyond my IT limitations!

[Emily, Conterminous Mod]

Thanks y'all! What a nice message to wake up to!

I found a similar spreadsheet in a Popsugar group I was in (not THE Popsugar group, but a spinoff) and thought it would be a fun adaptation. I never expected it to get so big or be something I relied on so much! So thanks for participating in it and making it even better!

[Steve]

Lindsay wrote: "Hi everyone, I just want to thank Emily and Steve for all their work on the spreadsheets. They are one of my most favourite parts of this group, and it makes me sad that I’m in another challenge gr..."

Thanks for the kind words! This is definitely Emily's baby, though; I just provide technical insight when I think I can contribute!

[viemag]

I need help with my 14th block on the spread sheet. I was rearranging my books and somehow goofed up the 14th square so when I entered my book, it somehow appears at the bottom and not where it should. Can someone help me fix it? Thank you Viemag

[Steve]

viemag wrote: "I need help with my 14th block on the spread sheet. I was rearranging my books and somehow goofed up the 14th square so when I entered my book, it somehow appears at the bottom and not where it sho..."

Fixed... You just cleared the formatting from the cell when you deleted the cell's contents. I've restored the formatting to it. (A trick: you can copy someone's cell next to yours that has the right formatting, then type your title in instead).

[Carol]

In case you need to know: I've just added a column (EH) named "Carol" on the A-F tab. Please let me know if there is anything I need to correct on it. Thanks!

[Emily, Conterminous Mod]

Thanks Carol. I'm not sure why all the extra columns on the spreadsheet were deleted.... but I appreciate you bringing it to my attention!

[Ellie]

Next year can we remove all the custom formatting on the name fields when it's copied to the new year? My cell thinks the default fill is a horrid orange colour and refuses to be white for some reason.

[Steve]

Ellie wrote: "Next year can we remove all the custom formatting on the name fields when it's copied to the new year? My cell thinks the default fill is a horrid orange colour and refuses to be white for some rea..."

Yes, I don't know what's going on! I highlighted all the blank cells at top, put them at default white background and black font, and it still then turns it red background with "fancy" script font in white despite the fact it's supposed to be plain. "Clear formatting" isn't working since it then splits the merged cells. Anyone know what to do??

[Chrissy]

Resetting the background on more than one cell at a time worked for me just now, but I’m not sure who chose their colors and who didn’t so I didn’t want to make any permanent changes and undid what I tried.

[Emily, Conterminous Mod]

I've pretty much been just deleting all the columns that I see have that orange background because I couldn't seem to turn it off either! Ellie, I can delete your column and put a new column in it's place with no orange, if you'd like.

[Ellie]

Emily wrote: "I've pretty much been just deleting all the columns that I see have that orange background because I couldn't seem to turn it off either! Ellie, I can delete your column and put a new column in it'..."

No worries, I've gone with pale grey. Maybe our spreadsheet's gone cranky in its old age.

[Emily, Conterminous Mod]

pst. I've added the 2024 tabs for the main ATY challenge, if anyone wants to go claim their column! Merry Christmas to those that are celebrating!

[John]

Emily wrote: "pst. I've added the 2024 tabs for the main ATY challenge, if anyone wants to go claim their column! Merry Christmas to those that are celebrating!"

For the newbie in this group, what is the link to the spreadsheet? The one at the top of this discussion group is for 2023.

[Emily, Conterminous Mod]

It's the same link, John. We will transition to it to 2024 when the year is over!

[Kendra]

I got to be first in the G-L page! I wont have any trouble finding my post this year.😜

[Jette]

That is my goal every year. Today, I *almost* made it onto the opening page of the G-L page. I'm in column O.

[Jill]

I only looked late yesterday for this, still manage to get P today !

[Nike]

I wanted to finish my list in the spread sheet today but I can't find my tab any longer. I think I made some notes there in October but now my name is nowhere to be found. Can anyone help me please. I've always found the spreadsheet very difficult to use, so maybe someone else can tell me what's happened or why it has been removed? Thank you

[Emily, Conterminous Mod]

I'm not seeing it Nike. Unfortunately, with so many people on the spreadsheet, sometimes things get accidentally deleted or moved around... looks like that might be the case for yours. Sorry about that!

[Steve]

Nike wrote: "I wanted to finish my list in the spread sheet today but I can't find my tab any longer. I think I made some notes there in October but now my name is nowhere to be found. Can anyone help me please..."

Nike, I found your column from the last time it was on the spreadsheet: September 13. I was able to recover it and put it in the 2023 M-Z tab at the very end, column EM.

[Emily, Conterminous Mod]

Thank you Steve!

[Vicki]

I can't find the spreadsheet for ATY 2024 that actually has the 2024 prompts listed.

[Steve]

Vicki wrote: "I can't find the spreadsheet for ATY 2024 that actually has the 2024 prompts listed."

It's on the community sheet: https://docs.google.com/spreadsheets/...

Emily, did you previously hide 2023 related tabs? Because someone unhid them if so. Or at least moved tabs beacuse I thought the 2024s were the first abs when I checked the other day.

[Emily, Conterminous Mod]

Yes, they were hidden. If you scroll along the bottom, the 2024 are there. I will likely give it another week then delete the 2023 tabs altogether, but I wanted to give people a chance to do last minute updates if they wanted to.

[Emily, Conterminous Mod]

For some reason, the total members is adding up to 311 on the A-F tab, 311 on the G-L tab, and only 309 on the M-Z tab.

Can anyone look at it and let me know what y'all think? I can't figure out what's up.

[Steve]

I see 312 on all 3 tabs. Maybe there was some sort of lag in the one tab updating? I'm not sure otherwise as all the formulas seem accurate.

[Emily, Conterminous Mod]

Well that does explain why I couldn't figure it out lol. They are all at 315 this morning so... we will chalk it up to a lag. Thanks for checking on it, Steve.

[Vicki]

Steve wrote: "Vicki wrote: "I can't find the spreadsheet for ATY 2024 that actually has the 2024 prompts listed."

It's on the community sheet: https://docs.google.com/spreadsheets/...-..."

Thank you!

[Jillian]

I noticed someone made a new post in 2024 weekly folder yesterday that they might have messed up the spreadsheet. Hopefully, someone here can make sure everything is good or reverse the mistakes.


Post:
https://www.goodreads.com/topic/show/...

[Steve]

Jillian wrote: "I noticed someone made a new post in 2024 weekly folder yesterday that they might have messed up the spreadsheet. Hopefully, someone here can make sure everything is good or reverse the mistakes.

..."

The link isn't working for me so I'm guessing it got deleted. At a glance, I don't see anything "messed up" about the spreadsheet. Either it's been fixed or wasn't actually messed up in the way that the other person thought they did.

[Jillian]

Thank you for looking. I can’t find the post anymore either, so it must have been deleted my the OP or a mod. I’m glad everything is good.

[Emily, Conterminous Mod]

Yes, I fixed the issue and removed the post! Thanks for looking out, guys. It was a minor issue.

[Perri]

Hey, I somehow combined my cell with my neighbors and don't want to mess with it too much since I might delete everyone haha. It's BB and BC ( Perri) The dwarf names?

[Sheena]

Perri wrote: "Hey, I somehow combined my cell with my neighbors and don't want to mess with it too much since I might delete everyone haha. It's BB and BC ( Perri) The dwarf names?"

I noticed this when I was updating my log. Looks like you just changed the text wrap formatting to overflow. (Maybe a cut and paste with formatting?) I switched it back to wrap.

[Perri]

Sheena wrote: "Perri wrote: "Hey, I somehow combined my cell with my neighbors and don't want to mess with it too much since I might delete everyone haha. It's BB and BC ( Perri) The dwarf names?"

I noticed this..."

No idea what I did but I appreciate the assist Sheena!

[Emily, Conterminous Mod]

Thanks Sheena! Looks like it's fixed!

[Jacqie]

I also did this when I tried cutting and pasting something, Jacqie H

[Carolyn]

Sheena wrote: "Perri wrote: "Hey, I somehow combined my cell with my neighbors and don't want to mess with it too much since I might delete everyone haha. It's BB and BC ( Perri) The dwarf names?"

When I've had problems in the past, I've entered a comment on the problem square. The moderators have fixed my problem, usually within a day after entering the comment. To enter a comment, click "Insert" then click "Comment".

[Perri]

TIL thanks Carolyn. The mods in this group are the BEST!!!

[Emily, Conterminous Mod]

Yes, Carolyn, that's the best way since I get an email when people comment! I'm not on Goodreads as much as I am checking my email.

[Bea]

I just updated my reading to this current week. This is good as last year I forgot to update it at all!