Calling all Spreadsheeters - ATY Community Spreadsheet

[Jillian]

I found your tracked voting tab helpful last year. I'm glad you are setting it up again.

[Jackie, Solstitial Mod]

May I suggest we start color coding the tabs at the bottom of the spreadsheet? Just something simple to make it more visually obvious which are the 2019 books completed pages vs the 2020 planning pages. I can do it if desired.

[dalex]

Emily wrote: "I'm thinking of leaving off the Recs tab this year... I feel like there were only a handful of people who used it, and the plans tab had plenty of suggestions for books that could cover that."

I agree that the Recs sheet was not really used and could be eliminated this year.

[Emily, Conterminous Mod]

Jackie, the color coding was a good idea! 2019 will be yellow, and 2020 is in redish pink.

Dalex, I'm glad to hear you say that since you were one of the few who were active on it.

[dalex]

Emily wrote: "Dalex, I'm glad to hear you say that since you were one of the few who were active on it."

I did add a few titles to the sheet but realized that it was a lot of work to remember books I'd read that fit the prompts and it really didn't seem worth it since most people don't rely on other people's recommendations (at least I assume that given the vast variety of books that are used for prompts).

[Pam]

My total count is too high. I had deleted a cell but I think the program is still detecting something. The problem may be in the "2 books related" cell(s). Is there a trick to clearing the cell other than deleting?

[Steve]

Pam wrote: "My total count is too high. I had deleted a cell but I think the program is still detecting something. The problem may be in the "2 books related" cell(s). Is there a trick to clearing the cell oth..."

If your actual count is 33, then you're all fixed, Pam. There was SOMETHING in those cells. I just pushed the "delete" key in the keyboard and that cleared the cells. It was likely a "space" from a spacebar that was in the cells. I only knew this because I had the same exact thing happen last year in my column! I physically counted each row by hand and only saw X books, but the count said X+1. Took me about 10 minutes to figure out what the heck was going on.

[Pam]

Steve wrote: "Pam wrote: "My total count is too high. I had deleted a cell but I think the program is still detecting something. The problem may be in the "2 books related" cell(s). Is there a trick to clearing ..."

Thank you Steve! I tried that several times and it didn't change the total. I wonder if it is something with my browser since I'm not using Chrome.

[Emily, Conterminous Mod]

Just a reminder for all you spreadsheeters! I've added tabs for the summer reading challenge (well, dalex added it, but teamwork!) and the 2020 plans and rejects!

Feel free to claim your spots for the Summer Reading Challenge and the 2020 list!

[Ellie]

I added myself to the summer challenge tab but the formulas aren't copied across that far.

[Emily, Conterminous Mod]

If I copy the formulas across, it will add the zeros into the averages, so I just update the formulas as I see people are adding to the tab! It should be fixed now.

[Emily, Conterminous Mod]

dalex, because you copied the numbers over to your column, your count total is skewed. You may want to use conditional formatting that turns the cells colors when the prompts are complete, rather than using the count.

[Steve]

Emily wrote: "If I copy the formulas across, it will add the zeros into the averages, so I just update the formulas as I see people are adding to the tab! It should be fixed now."

You could do an "averageif" instead of just average to look at any non-zero counts only. So =AVERAGEIF(E55:55,">0") should work.

[Emily, Conterminous Mod]

Ahhhh you're the best, Steve.

[dalex]

Emily wrote: "dalex, because you copied the numbers over to your column, your count total is skewed."

Sorry about that. I was going to change the whole thing but then discovered that the Total cell is locked & protected.

Could you possibly change the formula in the Total cell to =SUM(F4+F21+F38) ? Or if it is totally going to bother you that mine is different than everyone else's - which I would totally understand - just say so and I'll delete all my changes.

Thanks!

[Emily, Conterminous Mod]

No problem! I changed yours! I just wasn't sure how you wanted to handle it.

[dalex]

Emily wrote: "No problem! I changed yours! I just wasn't sure how you wanted to handle it."

Fabulous. Thanks!

[Ellie]

I completely forgot the summer challenge and rejects are on the spreadsheet. I was a bit sad that I wasn't able to tick off much on the main challenge with my recent reading, and then I remembered those! Saying that, my % turned green, so yay.

[viemag]

I just did this. It was fun to see, how many of us have read the same book for the same prompt, or have read the same book but used it to complete a different prompt.

[Emily, Conterminous Mod]

Viemag, I moved your list to the N-Z tab! Just wanted to give you a heads up in case you couldn't find it later.

[viemag]

Emily wrote: "Viemag, I moved your list to the N-Z tab! Just wanted to give you a heads up in case you couldn't find it later."

Thank you Emily! It took me awhile to find it, but I managed. :-)

[Sarah]

I need help from the spreadsheet gurus please! I am trying to add in a column next to mine to do the Summer reading challenge again but the name square keeps unformatting (is that a word? lol). My original is on column Z. Thanks!

[Emily, Conterminous Mod]

Fixed it for you, Sarah!

[Sarah]

That was quick! Thank you

[Kathryn]

I think I might have just deleted someone's name from column Z!! Apologies! I opened the spreadsheet up and Z was free, but then went off to my Excel spreadsheet and started typing in Z when I came back, but then I saw there was someone in column AA...

So sorry - but there wasn't anything entered into the rest of the Z column, I don't think!

[Kathryn]

I'm going to steer clear of column Z and use column AB, I think!

[Emily, Conterminous Mod]

Kathryn, are you talking about on the 2020 Planning tab? It looks like Traci is in AA and you are in AB, and there's no one in Z.

What probably happened is that you and Traci both went to create your lists at the same time, both typing in Z.... then both moving out of Z after realizing someone else was typing in there haha! Just some simple confusion -- the joys of Google Sheets! But, looking at the revision history, I can't see that you deleted anyone's progress. I'll just delete the empty column Z and we can all move forward.

[Kathryn]

Emily wrote: "Kathryn, are you talking about on the 2020 Planning tab? It looks like Traci is in AA and you are in AB, and there's no one in Z.

What probably happened is that you and Traci both went to create ..."

Thanks, Emily!!

[Traci]

Kathryn wrote: "Emily wrote: "Kathryn, are you talking about on the 2020 Planning tab? It looks like Traci is in AA and you are in AB, and there's no one in Z.

What probably happened is that you and Traci both w..."

Glad you figured it out! When I went in, your name was in column Z but nothing else, so I started in AA.

[Kathryn]

Traci wrote: "Kathryn wrote: "Emily wrote: "Kathryn, are you talking about on the 2020 Planning tab? It looks like Traci is in AA and you are in AB, and there's no one in Z.

What probably happened is that you ..."

I think I just panicked when I noticed your name after mine, Traci, and thought I must have inadvertently clicked on a box that wasn't at the end and had someone else's name in it!! Not helped by the fact that I had about about a dozen different tabs and windows opened - trying to do too many things at once and then getting confused about where I was up to with each of them!

[Traci]

FYI: Someone has this in my column (col Y) for "We didn't start the fire":
The Panic Virus by Seth Mnookin (vaccine)
The Other Einstein by Marie Benedict (Einstein)
Bleachers by John Grisham (baseball references)
Calico Joe by John Grisham (baseball references)

Maybe Kerry or Kathryn?
I'll probably delete it next week if it's still there.

[Kathryn]

Oh Traci - that’s me!! I’m not near the computer at the moment - can you cut and paste for me, please? So sorry!!

[Kathryn]

Alternatively, I can do it, if you don’t mind waiting til later today!

[Kathryn]

Traci wrote: "FYI: Someone has this in my column (col Y) for "We didn't start the fire":
The Panic Virus by Seth Mnookin (vaccine)
The Other Einstein by Marie Benedict (Einstein)
Bleachers by John Grisham (base..."

All done! I think I got confused when adding to the planning tab because my column in the spreadsheet for this year's challenge has somehow changed to column Y, and so then when I went to to the planning tab, because I'm column Z in that tab, I got mixed up and added to the wrong column!! I think I looked up at some point and thought "Oh, yes, I'm in column Y now" - and off I went!

[Rachel]

Emily, I just added my books to the Community Spreadsheet, and realized that I put everything in the wrong tab! I didn't notice at the bottom that there were separate tabs for different parts of the alphabet, so I added a column in A-F. I naturally assumed it was all on one page.

I'm not very good with spreadsheets in general so I don't want to risk moving it myself and messing anything up. Could you please move it for me? Sorry!

[Emily, Conterminous Mod]

I can take care of it when I get back to a laptop! Thanks for letting me know!

[Emily, Conterminous Mod]

Rachel, I've moved your list, along with a couple other people who also missed the tabs there.

I've also hidden the Summer Reading Challenge tab now that the challenge is over.

[Pam]

How do you do a hard return in a cell? I see that some members have a book per line. Mine are continuous because I can't figure out the formatting. Thanks Google Sheet experts!

[dalex]

Pam wrote: "How do you do a hard return in a cell? I see that some members have a book per line. Mine are continuous because I can't figure out the formatting. Thanks Google Sheet experts!"

On a Mac it's Command+Return

Or you can do Format - Text Wrapping - Wrap

[Avery]

For a PC, I hold down the "Alt" key and then press "Enter" :)

[Kathryn]

dalex wrote: "Pam wrote: "How do you do a hard return in a cell? I see that some members have a book per line. Mine are continuous because I can't figure out the formatting. Thanks Google Sheet experts!"

On a M..."

Huh! I was doing mine in a Word doc and then copying and pasting! Command-Return is much easier! I should have thought of that myself, but I didn't! Thanks, Dalex!

[Pam]

Thanks Dalex and Avery. Ctrl-Enter and Alt-Enter both work.

[Emily, Conterminous Mod]

Just found out that yesterday was National Spreadsheet Day... here's your reminder to update your ATY posts!

[dalex]

Emily -

There is an error message on the Fall Reading Challenge tab, the first prompt in October.

On the prompt tab, my percentage is divisible by 50 instead of 52. Could you edit that, please?

Thanks!

[Emily, Conterminous Mod]

Fixed! Thanks for pointing that out!

For some reason, your column alone was the only one dividing by 50... so strange. But it's fixed now. Congrats on finishing your challenge!

[dalex]

Emily wrote: "Congrats on finishing your challenge!"

Actually, I broke the "don't add books you haven't finished" rule. [sheepish grin] I just started the book this morning so it will be a few days before I'm officially done with the challenge.

Thanks for editing the spreadsheet!

[Emily, Conterminous Mod]

2020 tabs are open -- claim your place now!
(Just a reminder: place your name on the tab that indicates the letter your name starts with!)

[Jill]

Emily wrote: "2020 tabs are open -- claim your place now!
(Just a reminder: place your name on the tab that indicates the letter your name starts with!)"

Thanks Emily. Love this!

[Hélène]

Thank you, Emily! :-)

[Milena]

I love that I got column M for Milena.

Thank you for all your hard work, Emily. The community spreadsheet is one of my favorite things about this group.