Coopers Recipe Spreadsheet

[Thirsty Jim]

I lost my Excel some months ago and have been using Open Office Calc ever since.    These instructions are for Open Office Calc but  there is bound to be a very similar procedure in Excel...

If you want to view at once all recipes containing Real Ale you use the Standard Filter and select ' Can 1 '  and ' = ' and ' OS Real Ale' on the first line and

choose the operator ' OR ' 

and select ' Can 2 '  and ' = ' and ' OS Real Ale' on the second line.

All 13 recipes featuring Real Ale are displayed.

 

 

Edited March 11, 2020 by James of Bayswater
clarity

[Journeyman]

10 hours ago, James of Bayswater said:

Or under Can 1, if you choose OS Real Ale only those recipes that have the Original Series Real Ale (as the main recipe can) will be displayed.  Unfortunately I am not clever enough to be able to show those recipes where Can 2 is a Real Ale

Why don't you just choose OS Real Ale with the Can 2 filter ?  I must be missing something...

BTW if you do choose OS Real Ale with the Can 2 filter it displays the Coopers Vintage Ales 2013 to 2017.  They are the only recipes that use Real Ale in a 2 can brew.

You can use the [Standard Filter] option in the Can 1 drop down and choose like this...

[Thirsty Jim]

Cheers Journeyman.   I knew there would be an Excel equivalent. 

[Shamus O'Sean]

11 hours ago, Journeyman said:

You can use the [Standard Filter] option in the Can 1 drop down and choose like this...

Where is this magical "Standard Filter" you speak of.  See screenshot for options I have:

[Naughty Corner Brewing]

That’s an Open Office and Libre Office function. I think the equivalent capability in excel is in the Advanced Filter @Shamus O'Sean

[Journeyman]

11 hours ago, Shamus O'Sean said:

Where is this magical "Standard Filter" you speak of.  See screenshot for options I have:

Yes, Open Office in Linux. Finally there's a plus side to being in Linux.  Been a bit long since I played in Excel but Advanced Filter should allow it?

This is mine...

Edited March 12, 2020 by Journeyman

[Shamus O'Sean]

Thanks @NewBrews and @Journeyman, I had already checked out Advanced Filter.  It is not as advanced as Standard Filter.  Advanced Filter only lets you search in one column for AND/OR text.

I did find a way to search multiple columns based on some info from @James of Bayswater, but it was very cluncky and hard to include in the spreadsheet.

[Journeyman]

1 hour ago, Shamus O'Sean said:

Thanks @NewBrews and @Journeyman, I had already checked out Advanced Filter.  It is not as advanced as Standard Filter.  Advanced Filter only lets you search in one column for AND/OR text.

I did find a way to search multiple columns based on some info from @James of Bayswater, but it was very cluncky and hard to include in the spreadsheet.

I used to do it all the time in Excel - I better move up my partitioning and Win install in  priority I think.  (had to produce Helpdesk stats and the cheapo Education Dept wouldn't buy a real system)

EDIT: Bejaysus - Linux Mint is doing a Windows-size upgrade!! A normal one is large at maybe 50 MB - this is 750 MB. That's what Microsoft normally does.

So, update, backup, partition, install Windows and then pissfart around trying to get Win to be the SECOND choice for OS... The things I do for beer.

Edited March 13, 2020 by Journeyman

[Thirsty Jim]

Of course you could always just Ctrl+F and find every instance of Real Ale on the spreadsheet.    It works.  The only problem is that it also finds the brew title Unreal ale unless you check the 'Match entire cell contents' box.

There is always a work around.

 

 

[iBooz2]

On 3/6/2020 at 9:34 AM, Journeyman said:

I've attached the latest (I think) version of the SS.

2016 Kit & Extract Beer Designer V4.2.xls 1.11 MB · 6 downloads

Hey Mark, I grabbed a copy of this one as it was 1 version newer from what I had been using but it crashes to a debugger whenever I try and save a simple recipe.  When I do the same recipe in the previous versions it saves fine.  With all due respect and apologies if I am mistaken but DYFWI before uploading?  Just wondering.

Cheers - AL

 

[Journeyman]

15 hours ago, iBooz2 said:

Hey Mark, I grabbed a copy of this one as it was 1 version newer from what I had been using but it crashes to a debugger whenever I try and save a simple recipe.  When I do the same recipe in the previous versions it saves fine.  With all due respect and apologies if I am mistaken but DYFWI before uploading?  Just wondering.

MNFWN  That's as it was downloaded - I know that because as soon as I use it I rename it. e.g. this one, the one I use is called "Kit and Extract Beer Designer v4.2a.xls" - in Nemo (Linux file explorer) the original displays as "2016 Kit & Extract Beer Designer V4.2.xls" So when I got it, I made changes to font size (old eyes) and added in Coopers Ale & Golden Promise Grains and change Nott %'age to 78% (where I get it from that what they give as the value - others say 75% to 80%) then do a [Save As] and remove the front part.

When I do a recipe it gets renamed as the recipe. So the only time I even open the original is that 1st time and it doesn't get saved. 

Note that I have never been able to Save a recipe - there are several features I can't get working in LibreOffice Calc.

Did you try re-download?

Here's the modded one - do you get the same issue?

Kit and Extract Beer Designer v4.2a.xls

[iBooz2]

All good Mark, I knew you would probably go about it the same way I do.  Yep basically do the same with any download I use. Leave the original and copy / paste it to a working folder then rename it before IFWI or in some cases before IFIU.

I use Excel 2010.  Have not had a chance to play with this since the weekend so will see how it goes when I get back to it later.

Cheers - AL

 

[Shamus O'Sean]

Coopers Recipe Spreadsheet - April 2020

Hi Team

Latest Coopers Recipe Spreadsheet is attached.  April 2020 ROTM added: Belgian Chocolate Stout & Sweet Nectar Kolsch.  Click on the recipe name in red for pdf's of these recipes.

I have renamed the Coopers Recipe Spreadsheet to exactly that.  I hope this change does not affect your use of the spreadsheet.  I have also added the Release Date for almost all ROTM going back to early 2017 when I started brewing.

Coopers Recipe Spreadsheet V2.9.xlsx

Spreadsheet functionality. 

• You can click on the recipe name to jump to the recipe on the Coopers website (for those in au only)
• The little triangle icons beside each column title are auto-filters and can be used to sort the spreadsheet by the respective column
• You can also use the drop boxes of the variables in each column to show those variables you want to see.  
• For example, under Difficulty if you choose the Easy variable then only those recipes rated as Easy will be displayed
• Or under Can 1, if you choose OS Real Ale only those recipes that have the Original Series Real Ale (as the main recipe can) will be displayed.  Unfortunately I am not clever enough to be able to show those recipes where Can 2 is a Real Ale
• Any queries?  Post them to this thread.

[Instigator]

8 hours ago, Shamus O'Sean said:

I have also added the Release Date for almost all ROTM going back to early 2017 when I started brewing.

Bloody fantastic! I was only just wondering about release dates yesterday.

Thanks again, bloke.

[Thirsty Jim]

Shamus,  the hyperlinks to Anarchy IPA, Earl Grey IPA and  K9 Quencher need updating

[Shamus O'Sean]

1 hour ago, James of Bayswater said:

Shamus,  the hyperlinks to Anarchy IPA, Earl Grey IPA and  K9 Quencher need updating

Thanks James,

I am not sure why this happens.  I think that I stuffed up.  You may have noticed that column J is hidden and shows the URL for the recipe.  These links are correct and they are how I fixed the ones you pointed out.  I did a quick check and had to adjust the IPA recipe because of how Excel reacts depending where you have the mouse in the field.

Cheers Shamus

[Shamus O'Sean]

Coopers Recipe Spreadsheet - April 2020

Hi Team

@James of Bayswater has pointed out some short-falls in the Coopers Recipe Spreadsheet, so I have made the corrections he raised.  For those that have already downloaded the version above do not worry too much.  When I post next month's spreadsheet, these corrections will be included, so you could wait until then.

Latest Coopers Recipe Spreadsheet is attached.  April 2020 ROTM added: Belgian Chocolate Stout & Sweet Nectar Kolsch.  Click on the recipe name in red for pdf's of these recipes.

Coopers Recipe Spreadsheet V2.9.1.xlsx

Spreadsheet functionality remains the same: 

• You can click on the recipe name to jump to the recipe on the Coopers website (for those in au only)
• The little triangle icons beside each column title are auto-filters and can be used to sort the spreadsheet by the respective column
• You can also use the drop boxes of the variables in each column to show those variables you want to see.  
• For example, under Difficulty if you choose the Easy variable then only those recipes rated as Easy will be displayed
• Or under Can 1, if you choose OS Real Ale only those recipes that have the Original Series Real Ale (as the main recipe can) will be displayed.  Unfortunately I am not clever enough to be able to show those recipes where Can 2 is a Real Ale
• Any queries?  Post them to this thread.

[Thirsty Jim]

Shamus,    The only reason I was aware those links weren't working is because I use Open Office to open the spreadsheet and in OO all hyperlinks are shaded by default so those three I pointed out were unshaded and stood out like the dog's proverbials.   

I am not sure why you need column J.  In Excel I would select the cell with the brew name, right click and chose 'Add Hyperlink' from the menu.

BTW, if anyone else is using Open Office and wants to get rid of the shading of the brew names then choose TOOLS/OPTIONS/ APPEARANCE /Text Document/ uncheck  'Field Shadings' or chose 'White' as the shading color.

 

[Instigator]

A couple of requests - if you want. Not really a big deal either way.

In the Can 1 column, Innkeeper's Daughter is misspelled as Inkeeper. The actual online recipes for Coopers Sparkling Ale and Marilyn's Secret have this same misspelling.

In the Yeast 1 column, there is both T-58 and Safbrew T-58. Would it make sense to combine these?

[Naughty Corner Brewing]

6 hours ago, Instigator said:

A couple of requests - if you want. Not really a big deal either way.

In the Can 1 column, Innkeeper's Daughter is misspelled as Inkeeper. The actual online recipes for Coopers Sparkling Ale and Marilyn's Secret have this same misspelling.

In the Yeast 1 column, there is both T-58 and Safbrew T-58. Would it make sense to combine these?

You must be a business analyst or a test analyst in your day job?

[Shamus O'Sean]

8 hours ago, James of Bayswater said:

The only reason I was aware those links weren't working is because I use Open Office to open the spreadsheet and in OO all hyperlinks are shaded by default so those three I pointed out were unshaded and stood out like the dog's proverbials.   

I am not sure why you need column J.  In Excel I would select the cell with the brew name, right click and chose 'Add Hyperlink' from the menu.

No worries James.  That sounds like a handy feature of Open Office.

I have column J because that is what I started the spreadsheet with.  It was only later that I thought I could add a hyperlink to the recipe name.  I did that when I was trying to make the spreadsheet less wide. 

I have just kept populating column J ever since.  It only takes a second because I copy the link into that column at the same time as I insert the hyperlink onto the recipe name field.

[Shamus O'Sean]

7 hours ago, Instigator said:

A couple of requests - if you want. Not really a big deal either way.

In the Can 1 column, Innkeeper's Daughter is misspelled as Inkeeper. The actual online recipes for Coopers Sparkling Ale and Marilyn's Secret have this same misspelling.

In the Yeast 1 column, there is both T-58 and Safbrew T-58. Would it make sense to combine these?

Hi Instigator, Thanks for the pick-up's.

Done and done.  I will not bother reposting the spreadsheet with these corrections until next month when the new ROTM come out.  You second point made me look at the yeasts a bit closer and tidy up some other names for more consistency.  For example Nottingham was mentioned under three different names so I have rationalised them.  Same with Lallemand Munich Classic Wheat yeast.

[UncleStavvy]

No links from spreadsheet working this morning, also recipe link on forum home page not working. No biggie, Ive seen this happen last Friday and put it down to website maintenance.

[Instigator]

4 hours ago, NewBrews said:

You must be a business analyst or a test analyst in your day job?

I only picked those things up because I was filtering on them. Didn't even notice the Nottingham and other ones.

3 hours ago, Shamus O'Sean said:

Hi Instigator, Thanks for the pick-up's.

Done and done.  I will not bother reposting the spreadsheet with these corrections until next month when the new ROTM come out.  You second point made me look at the yeasts a bit closer and tidy up some other names for more consistency.  For example Nottingham was mentioned under three different names so I have rationalised them.  Same with Lallemand Munich Classic Wheat yeast.

Great work, bloke. As I said above, I only noticed because I was using it, and I was using it because it's useful. Thanks for your efforts.

[Shamus O'Sean]

5 hours ago, UncleStavvy said:

No links from spreadsheet working this morning, also recipe link on forum home page not working. No biggie, Ive seen this happen last Friday and put it down to website maintenance.

Seems to be working now. Cheers