2019 Team Competition Tracker/Google Sheet Central

[Brian]

My totals show up on my participants page but not on the overall ranking page??? did i do something wrong??

[Timelord Iain, Tech Support]

Diana Stormblessed wrote: "This might be a stupid question, but why do the points change in all the fields if you change Y/N on Audiobook? Shouldn't it only change the points on the "Page points" field?"

We asked the mods this question a ton of times, to make sure we got it right... reading the book vs going audio isn't just 25% bonus points, like BR points are 33% bonus, or 10% challenge bonus... it's 25% bonus pages, which thus affects the BR/Challenge points... it's similar to how being a graphic novel loses you 75% of the page count, which affects BRs/Challenges...

[Timelord Iain, Tech Support]

Brian wrote: "My totals show up on my participants page but not on the overall ranking page??? did i do something wrong??"

It's there now... we've noticed a similar delay in general, with points making it to the Main Page... it seems to be a symptom of all the formulas... it generally updates, given time, or if someone changes something else somewhere...

[Brian]

Copy that’s. Thanks man!!!

[Virginie]

That's not stupid; that's the question we Nerd Squad kept pestering the mods about :p

It was decided that non-audio books get a 'page multiplier' of 1.25 (similar to graphic novels that have a 0.25 multiplier). It's like you virtually add 25% more pages to the book.
Since challenge/BR bonus points are based on the page points, it affects them as well.

EDIT: nevermind, I didn't see there was another page of comments... Iain answered :p

[Diana Stormblessed]

Timelord Iain (Eeyawn) wrote: "Diana Stormblessed wrote: "This might be a stupid question, but why do the points change in all the fields if you change Y/N on Audiobook? Shouldn't it only change the points on the "Page points" f..."

Got it. I thought it was just bonus points, not bonus pages. That's some serious audiobook penalty. I get it why they did this, but it still sucks for me, who's like 90% audiobook.

[Timelord Iain, Tech Support]

Diana Stormblessed wrote: "Timelord Iain (Eeyawn) wrote: "Diana Stormblessed wrote: "This might be a stupid question, but why do the points change in all the fields if you change Y/N on Audiobook? Shouldn't it only change th..."

Me too... watching it in action is kinda painful... glad I'm not pushing for top points again this year... only way I could is if I went back to all BRs, all the time...

If i had to guess, Julia & Soo will be in tight competition for #1-2 until Julia starts camp in the summer, or Soo gets sidetracked with other extracurricular activities...

[Soo]

I'm sure 75% of my intake will be audiobooks so I totally have the penalty working against. I mean, I started Ember short story (88 pages) on Jan 1st and I only read 35 pages. LOL

I'm already trying to figure out how to get at least 10 pages of S (Ship of Theseus) in everyday or make sure I do a few hours on the weekend for that BR. I want to wrap that one up by middle of February. That way I can enjoy the discussions with friends before my trip.

Making time to read print is going to be work for me and that puts a cramp on some of the "planned" reading I have in place. I'll make it work somehow!

[Timelord Iain, Tech Support]

Soo wrote: "I'm sure 75% of my intake will be audiobooks so I totally have the penalty working against. I mean, I started Ember short story (88 pages) on Jan 1st and I only read 35 pages. LOL

I'm already tryi..."

I got my 2nd copy of Ship of Theseus in days ago, and it's still in the shrink-wrap, because, again, sick :(

That's my problem with physical reading as well... it's so easy to do other things than physical reading... I tried to join Wars of Light & Shadow last year, which lacks audio, and made it through 7 chapters in a month...

[Kathrine]

My calender thing is broken :(

I tried fixing it to dd/mm/yy like Iain wrote, but I did something wrong and now it does the exact opposite of what I write, so if I write it like: 3/1/19 it changes it to 1/3/19, and if I write 1/3/19 it changes it to 3/1/19. No clue why

[Timelord Iain, Tech Support]

Kathrine wrote: "My calender thing is broken :(

I tried fixing it to dd/mm/yy like Iain wrote, but I did something wrong and now it does the exact opposite of what I write, so if I write it like: 3/1/19 it change..."

I think I misunderstood the usefulness of my suggestion before... it's not reading what you write in the order we structured it, it's just rearranging the info...

You have 2 options:

1) The Karishma option: change the format again to read January 3rd, 2019... so the text will tell you if you did it right...

2) The Iain option: highlight the column again, from a15 down, right click, data validation, Date

The benefit of option 2, is that you can now just double click the date column and pick the date from a mini-calendar, and the sheet will input the date for you...

EDIT: I did both of these things... if anyone else wants this setup, just follow the directions above...

[Saar The Book owl]

Can anyone look at my sheet? In the rows L,M and N I get an error.

[Timelord Iain, Tech Support]

Saar The Book owl wrote: "Can anyone look at my sheet? In the rows L,M and N I get an error."

Fixed... K1 needed to be K2... also fixed your point columns to be visible when not 0.0

[Narilka]

I just gave my sheet a test drive. It had an error at first and as I was about to type a post here, it updated and corrected :) I'm guessing I just had to be more patient as I enter items. I've deleted my dummy book so should be good to go.

Thanks to everyone for all your hard work!

[Kathrine]

Timelord Iain (Eeyawn) wrote: "I think I misunderstood the usefulness of my suggestion before... it's not reading what you write in the order we structured it, it's just rearranging the info...

You have 2 options:"

Thanks, I went with your option 2 and now it works and doesn't completely hurt my European eyes 😂

[Saar The Book owl]

Timelord Iain (Eeyawn) wrote: "Saar The Book owl wrote: "Can anyone look at my sheet? In the rows L,M and N I get an error."

Fixed... K1 needed to be K2... also fixed your point columns to be visible when not 0.0"

Thanks, Iain :)

[Virginie]

Timelord Iain (Eeyawn) wrote: "2) The Iain option: highlight the column again, from a15 down, right click, data validation, Date"

WHAAAAAAT?!? I was ready to pester about the lack of option to make the little calendar pop-in. You just made my day! :D :D :D

[Timelord Iain, Tech Support]

Virginie wrote: "Timelord Iain (Eeyawn) wrote: "2) The Iain option: highlight the column again, from a15 down, right click, data validation, Date"

WHAAAAAAT?!? I was ready to pester about the lack of option to mak..."

I love the calendar pop-in... ever since I figured out about it, when trying to fix a date last year, by double-clicking to enter edit mode...

[Frank]

Em Lost In Books wrote: "I entered 226 pages in my sheet but on the Main page I have 300+ pages..."
I seem to be having the same problem, entered my first book on my tab, nothing totaled within the tab nor on the totals pages.

[Timelord Iain, Tech Support]

Frank wrote: "Em Lost In Books wrote: "I entered 226 pages in my sheet but on the Main page I have 300+ pages..."
I seem to be having the same problem, entered my first book on my tab, nothing totaled within the..."

You didn't plug in today's date... I moved the headers down so that's more obvious in the future...

[Bea]

Just entered points and the "book read" number and the page points/BR bonus numbers are not shown on my sheet. (rows G, L, M and N) as well as the books/pages etc. read in January.
Could one of the spreadsheet gods investigate? :)

[Timelord Iain, Tech Support]

I'll take a look when I get home, but most of the LMN problems are just because the font color matches the background color... seems like a relic of all the team colored template copying... default font color should be white, and the conditional formatting makes it match the background for 0s

[Virginie]

Bea wrote: "Just entered points and the "book read" number and the page points/BR bonus numbers are not shown on my sheet. (rows G, L, M and N) as well as the books/pages etc. read in January.
Could one of the..."

BR/Challenge bonus are not showing 'cause you put N in the relevant columns. I see the page points normally from my computer.

The rest was due to the fact you entered 'January 3rd' in text in the date column. It has to be a date format for all the formulas to work. I went ahead and fixed your sheet so that you can enter a date by double-clicking on the cell, and using the mini-calendar that pops up.

[Bea]

Virginie wrote: "Bea wrote: "Just entered points and the "book read" number and the page points/BR bonus numbers are not shown on my sheet. (rows G, L, M and N) as well as the books/pages etc. read in January.
Coul..."

Ah, ok. I thought we could enter in text format. Thanks for fixing it! :)
My monthly overviews are now gone, though...

[Virginie]

They're only hidden. You have a little + button on the left of the 'headers' (row 3) to show/hide them.

[Bea]

Virginie wrote: "They're only hidden. You have a little + button on the left of the 'headers' (row 3) to show/hide them."

Oh, I didn't know that, sorry to bother you again.
I'm such a noob when it comes to everything technical 0_0

[Timelord Iain, Tech Support]

Bea wrote: "Virginie wrote: "They're only hidden. You have a little + button on the left of the 'headers' (row 3) to show/hide them."

Oh, I didn't know that, sorry to bother you again.
I'm such a noob when it..."

No worries... it takes practice to learn the ins and outs of what Google Sheets / Excel is capable of... me and Virginie and Rob are constantly learning cool new things we can do :)

[Virginie]

Yup! Like Iain said, no need to apologize for that :)
I know I sound a little dry and pissed off when I post here but I'm not, I promise! I'm just trying to be clear and get to the point :)

[Em Lost In Books]

Can someone look into my sheet?

Total tally for Jan shows only 1 book and it's corresponding points. it does not add points/pages for second book.

BR points and Challenge points for second book also doesn't add up in Participant data sheet.

[Timelord Iain, Tech Support]

Em Lost In Books wrote: "Can someone look into my sheet?

Total tally for Jan shows only 1 book and it's corresponding points. it does not add points/pages for second book.

BR points and Challenge points for second book ..."

Just running slow, I think... working now, and I don't think Virginie's been in the sheet in the last 5 minutes :)

[Yanique, Thread Master]

@Virginie can we make changes to one of my graphs when you have a free minute? It's a simple change but I don't want to mess with the formulae and cause any other problems.

I left you the details on my sheet.

[Em Lost In Books]

Timelord Iain (Eeyawn) wrote: "Just running slow, I think... working now, and I don't think Virginie's been in the sheet in the last 5 minutes :)..."

12 hour slow? I added my second book around 9 in the morning, now it's 9 in evening...

[Virginie]

@Manju: there was a slight problem with row 16 (your second book). There's a formula in Column P (hidden) that's vital for all total calculations. For some reasons, it was deleted from row 16. Don't really know why... That's the reason your book wouldn't show up in totals/monthly tally. Anyway, it's fixed now :)

[Virginie]

Yanique wrote: "@Virginie can we make changes to one of my graphs when you have a free minute? It's a simple change but I don't want to mess with the formulae and cause any other problems.

I left you the details..."

Coming right now :)

EDIT: done (I think) - details on your sheet.

[Em Lost In Books]

Virginie wrote: "@Manju: there was a slight problem with row 16 (your second book). There's a formula in Column P (hidden) that's vital for all total calculations. For some reasons, it was deleted from row 16. Don'..."

thanks!

I inserted a row and copied another row and pasted it to this new row. since columns are visible upto O only, that hidden column formula did not get copied and hence the error. Just checked it. So I can not insert row as column P is hidden and that important formula won't be copied, right?

[Timelord Iain, Tech Support]

Em Lost In Books wrote: "Timelord Iain (Eeyawn) wrote: "Just running slow, I think... working now, and I don't think Virginie's been in the sheet in the last 5 minutes :)..."

12 hour slow? I added my second book around 9 ..."

When I loaded your sheet, your second book was added... you had 12.something points

EDIT: OH... I was looking at the top row formulas, not the monthlies... my bad...

[Timelord Iain, Tech Support]

Em Lost In Books wrote: "Virginie wrote: "@Manju: there was a slight problem with row 16 (your second book). There's a formula in Column P (hidden) that's vital for all total calculations. For some reasons, it was deleted ..."

If you insert a row above/below, generally the formulas/layout of the relative row you are inserting from are applied to the new row... if you insert 1 row below row 17, the new row should have everything row 17 does...

If you think there's a problem, you can generally fix it without revealing the hidden column... just highlight an old row by clicking on the number to the left... Ctrl-C... highlight the new row... Ctrl-V... and the sheet will apply all the formulas/layouts, relative to the current row...

[Em Lost In Books]

Timelord Iain (Eeyawn) wrote: "If you think there's a problem, you can generally fix it without revealing the hidden column... just highlight the row by clicking on the number to the left... Ctrl-C... highlight the new row... Ctrl-V... and the sheet will apply all the formulas/layouts, relative to the current row... ..."

it works! thanks. :D

[Virginie]

Yup, the easiest way is to copy the entire row, like Iain explained you to do. Glad we found a way to make it work for you :D

[Yanique, Thread Master]

Virginie wrote: "Yanique wrote: "@Virginie can we make changes to one of my graphs when you have a free minute? It's a simple change but I don't want to mess with the formulae and cause any other problems.

I left..."

It's perfect! Thanks! I can make no promises about slowing down though, LOL!

[Frank]

Timelord Iain (Eeyawn) wrote: "Frank wrote: "Em Lost In Books wrote: "I entered 226 pages in my sheet but on the Main page I have 300+ pages..."
I seem to be having the same problem, entered my first book on my tab, nothing tota..."
Thank you Iain, much appreciated.

[Em Lost In Books]

Why not team ranking change with the points like last year?

[Timelord Iain, Tech Support]

Em Lost In Books wrote: "Why not team ranking change with the points like last year?"

Did a google search and figured out how to accomplish that... dragged the old rankings down to rows 61-64, and then applied =sort(A61:D64, 2, true) to the old ranking board to sort it...

Virginie might want to move the old rankings to some hidden rows further down, in the future, but we have the functionality for now :)

And now I know how Rob made the rankings last year, WOO :) ... always learning...

[Timelord Iain, Tech Support]

You started the snowball rolling again... you're rankings comment/fix/upgrade has me thinking of more things we can do with sorting/rankings...

[Timelord Iain, Tech Support]

You Didn't Ask For It... But You Got It!!!

Not only does the Team Rankings sort now, it sorts by points AND by pages... we added a switch to cell c10 to flip back and forth...

[Virginie]

Timelord Iain (Eeyawn) wrote: "You started the snowball rolling again... you're rankings comment/fix/upgrade has me thinking of more things we can do with sorting/rankings..."

Yup, the snowball hit me and I am now merrily rolling with it into spreadsheet-land.

[Seán]

I don't think my Audio books are registering at 75% page count - do I need to do anything other than put a 'y' in the Audio column for this to take effect? Points are still based on the total page count for the book.

[Timelord Iain, Tech Support]

Audio isn't worth less points... non-audio is worth MORE...

[Lundos]

Does anyone else have trouble accessing the tracking system file? It just say loading.

Also, very well done Virginie! Great work.

[Timelord Iain, Tech Support]

I just refresh the page when that happens sometimes... happens to me with any Google Sheet sometimes...