Thursday, 14 February 2019

learning some google sheet formularizing; scout emails and software release versions by site

Matching installed sites and versions and showing selected sites not on latest version.

Taking an item from another sheet . . (a latest published release string)
=IMPORTRANGE("", "Summary!C1")

Other sheet "ProdLineSites" created to pull in data on siteids, type, installed version
=IMPORTRANGE("", "Sites!B:H")

List ALL SITES siteids in column C and by type (column B)   B2 is the site type in heading column
Dev QA S1 S2 L
=FILTER('Form Responses 1'!$C2:$C1000,'Form Responses 1'!$B2:$B1000 = B2)

List all sites selected by 1 in G column: find siteids(C), by type(B), H2  is the site type in heading column
Dev QA S1 S2 L
=FILTER('Form Responses 1'!$C2:$C1000,'Form Responses 1'!$B2:$B1000 = H2,'Form Responses 1'!$G2:$G1000 = 1)

A single column latest release from sites

Selected LAST SUCCESSFUL VERSION, get versions of sites from the production line version .. formula copied .. H3 etc (H3:L1000) refers to the selected siteids. vlookup matches the siteid(B:) and pulls out the version(D:==+3)
Dev QA S1 S2 L

To show errors better if site has no version . .  Which Happened!

And conditional formating colour . . .
If version doesn't match the latest highlight it as red . . .
To make rule based on reference to cell values use "Custom formula is" . . .

Organising contacts . . . making comma-separated email lists . . . by leader type

A sheet per scout group. Row 4 has headings:
Group nameNameSectionemail addressTraining LevelSkills haveSkills would like

Email list sheet A1 can be changed to the leader type wanted.

A1: Scout
A2: ^^^ Change me ^^^

$B1 has group names  (i.e. B1 C1 D1 E1 etc)   and J1 has email lists

B3:IN has . . . INDIRECT uses $C6 as sheet name (scout group name) 
address pulls data from the other sheet relative to the current sheet. 
find matching section in $A$1 e.g. "Scout" to Section column C in sheet B$1 (sheet match group name)
if found pull in the email address entry in column D
iferror or not found return empty string

B2:I2 has . . .  =iferror(join(",",filter(B3:B60, NOT(B3:B60 = "") )),"")
that joins all the email addresses in column together with "," 
filter to skip blanks 
iferror fill in with blank if no match

J2 combines all email lists from B2:I2 together, filter to eliminate blanks
=iferror(join(",",filter(B2:I2, NOT(B2:I2 = "") )),"")

Saturday, 9 February 2019

BB(Blackberry the cat)'s visit to the vet

BB back from the vet. All good now :) She really didn't like going into carrier! We tried her going in head first but that doesn't work a few scratches! We upended it and in head first but really had to push and put each bit of her in. In vets later upend it, lower her in bum first, much easier(but still have to detach her from holding on as lowered in).
Back from the vet. Calm. Happy.

Kate and I got some scratches from putting her into cat carrier, we don't mind though.

She miaowed in the car a good bit on way there, with Maeve in back of car, calmer on way home. Happy now back in her own house. There were some dogs at the vets before us.      

She is BB in the vet's computer. We left her out in vet's inspection room while filling in details. She was looking for hiding place behind computer or up high eventually settled under the checkup table. One person holding her by shoulders works well, vet inspected mouth and rest of her, injection for leukemia vaccination she didn't move, vet had a dispenser for worm pill a bit like plastic injection mini rubbish pick up thing, open mouth, thing in, click, pill gone. We should get one of those! Weigh scales wide surface area is good. She is 3.6kg. Healthy.      

If outside hunting needs to keep up her worming meds once a month. We got a pack. Flea meds from woodies/supermarkets vet says do not absorb into skin and enter blood stream so they are surface effective only. The pharmaceutical ones more expensive but the vet reccomends them. Food dry food is good for teeth but he said wet about a third of the food, this makes it easier to digest.     Leukemia booster needed in 3 weeks time, can do flu vaccination also at that time so that after that yearly booster for leukemia vaccination and flu can be done together.

107 euros cost

Bb's food, meds, other equipment and supplies

Food dry cat food we tried her on another brand once and she was not interested.