G1 Spreadsheet by Set and Year with Percentages - Complete

[Baby Crumpet]

This is so helpful! Thank you for making this and sharing it

[buttonso]

This will be most helpful! Thank you for sharing!! 

[Beth3346]

Thank you for sharing.  I'm trying to complete my US and European G1s.  This will be so helpful

[tuneful87]

I forgot to mention that the cells the totals are contained in will change from red to green once you have completed a set or year.

[Beth3346]

Tuney, I can't find Long-Haired Bow-Tie

[tuneful87]

Tuney, I can't find Long-Haired Bow-Tie

Thanks, I had a feeling there might be more mistakes out there. I will fix the template now, but if you've already started filling everything in, you'll need to adjust accordingly. Sorry about that.

[Salli]

Love this! Thanks for sharing Tuney!

[tuneful87]

Love this! Thanks for sharing Tuney!

Welcome

Beth: The template is all fixed. If you need to make the adjustments yourself, just a few things that need to be done:

1. Cut/paste the totals down one row. Insert Bowtie above.

2. Cut/paste the totals from the sea pony column to the left (since both total 6 ponies, it's easier to do this than to have to create a new rule)

3. Change the number beside Year 3 Earth ponies from 5 to 6.

4. For Earth pony owned and missing totals, right click/Conditional Formatting. You will see that there are now two sets of rules in conflict with each other. One is looking for ponies between the numbers 1 and 5 while the other (which you just pasted in step 2) is looking for ponies between 1 and 6. Click on each rule for 1 to 5 (one at a time), click the apply to range grid and delete only the cells K47 and K48 but leave all the other cells as is. Then click done.

5. Unhide column C, and bump the total up 1 in cell C41. Go to the very top of column C and you will see the totals listed for US and full totals, which feed into the grand total formulas. These will need to be bumped up once too. But I just realized this should have been done automatically, so let me fix that on the template. Ugh...sorry about that.

6. Double check that the set and year formulas are now looking to the right cells for Earth Ponies Year 3.

These are basically the steps you'd need to take in adding/removing a pony from any set. The conditional formatting is all based on how many ponies are in each year, so look at any other sets with a total of 6 ponies to see how the conditional formatting and formulas should look.

Let me know if you have any questions.

[Beth3346]

Thanks Tuney

[Sweet_Stuff]

This is awesome, thank you for sharing 

[Miss_Pompon]

Amazing ! Thank you for the hard work !

[tuneful87]

You're welcome. I hope you find it useful and that I haven't made any other mistakes!