• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automation of a Puppy Weight Chart

Seahorse

Seahorse

Member
Hi, I normally poke through here for work related matters, however my first post is for home use!


Ruby, our Doberman is expecting 11 puppies next week. One thing we must do is track their weights to ensure they are growing and the smaller ones get extra feeds.


http://www.dead-fish.com/Files/130511%20-%20Puppy%20Weight%20Tracker.xlsx


I have put together this spreadsheet as the frame invented a birthday to test, but I want to avoid editing the chart range every time another day is added, it needs to cover their first 10 weeks.


The second thing I want to automate is which puppy is heaviest and which 3 are the smallest with their weights. Again, you can do this by eye, but I don't want to do so if it can be automated.


I would appreciate some advice on how to achieve this.
 
Hi ,


Can you check this file ?


http://www7.zippyshare.com/v/79974601/file.html


I have made the X-axis and the first chart series dynamic , so that as you add more data , you do not have to edit the chart range ; can you replicate this for the other 10 series ?


Once you confirm this , we can move on to displaying the heaviest and smallest puppies ; how do you want to show this ? By using Conditional Formatting ?


Narayan
 
I can see the difference in the first one which happily extends the line when you add another day's weight, how do I replicate the extended selection? I tried manually editing the second series, but immediately broke it by running the range out to the right.


Conditional formatting their names would be fine or perhaps another few cells separately. My reason for this is each puppy will get a coloured collar and I intended to change the char lines and the relevant rows to matching colours.
 
Hi ,


If you go into the Name Manager , you will see the following 3 named ranges :


1. Start_Date referring to :
Code: 
='Puppies!'!$G$25


2.  [b]X_Axis referring to : [code]=Start_Date:INDEX('Puppies!'!$25:$25,COUNTA('Puppies!'!$25:$25)+1)


3.  [b]Series_1 referring to : [code]=OFFSET(Start_Date,1,,,COLUMNS(X_Axis))


In the chart , I removed your first series , which you had named One , and introduced the named range [b]Series_1. Click on the chart , then on Select Data , and see the definition of the first series : ='130511 - Puppy Weight Tracker.xlsx'!Series_1[/b]


If you want to replicate what has been done for the remaining series , just introduce named ranges Two , Three , Four ,... , and refer them to :


=OFFSET(Start_Date,2,,,COLUMNS(X_Axis))


=OFFSET(Start_Date,3,,,COLUMNS(X_Axis))[/code]


=OFFSET(Start_Date,4,,,COLUMNS(X_Axis))[/code]


and so on.


In the chart , use the named ranges as series , by :


='130511 - Puppy Weight Tracker.xlsx'!Two


='130511 - Puppy Weight Tracker.xlsx'!Three[/b]


='130511 - Puppy Weight Tracker.xlsx'!Four[/b]


and so on.


Narayan
 
OK, ranges created:


http://www.dead-fish.com/Pictures/Excel/Capture.JPG


I removed the old series from the chart and put


='130511 - Puppy Weight Tracker.xlsx'!Two


In the Series Values box:


http://www.dead-fish.com/Pictures/Excel/Capture2.JPG


However this fails with the following error:


http://www.dead-fish.com/Pictures/Excel/Capture3.JPG


Looking at the Series_1 as a template is has an infinite number of cells listed. Presume I a doing something fundamentally wrong?


Sorry to be so dense - current version of spreadsheet uploaded to:


http://www.dead-fish.com/Pictures/Excel/130511%20-%20Puppy%20Weight%20Tracker.xlsx
 
Hi ,


I downloaded your latest file , and added Series_2 by entering the following in the input box for the series values :


='130511 - Puppy Weight Tracker_1.xlsx'!Series_2


Referring to another comment of yours , when I select Series_1 in the Name Manager , and click in the Refers To box , the range G26:M26 is highlighted , which is correct.


I cannot understand what can be wrong ; can you try once more ?


Narayan
 
File name difference!


='130511 - Puppy Weight Tracker_1.xlsx'!Series_2


v


='130511 - Puppy Weight Tracker.xlsx'!Series_2


Second one works, all functioning now.


Revised save - http://www.dead-fish.com/Pictures/Excel/130511%20-%20Puppy%20Weight%20Tracker.xlsx
 
Sorted the fat & thin puppies detection with conditional formatting as suggested, so we are all set for the births next week - many thanks :)
 
that would be a honor. But I guess he has figured out better names for those already.


PS: If we get a dog (which should happen when I go back to India in August), I am going to call her INDEX.
 
Currently they are called Series_1, Series_2, Series_3 and so on. C22:C29 = Male and C30 = Female. Not sure if I will be completing Lines 31 & 32 yet.


http://www.dead-fish.com/Pictures/2013-05-17%2008.17.46.jpg


http://www.dead-fish.com/Files/130511%20-%20Puppy%20Weight%20Tracker.xlsx


using Top 3 and Bottom 3 to ensure the little ones get first feeds :)
 
Hi, Seahorse!

Congratulations for the newborns.

Regards!

PS: BTW, puppies are cuter than our KeyMaster... :p
 
Hi,


Puppies are now a week old, and I've added another tab to track daily weight gain, which is where I realised the conditional formatting on either tab is taking the weeks worth before showing top 3 & bottom 3.


What I want to see is the heaviest/lightest 3 dogs on each day, but I don't know how to limit the conditional formatting per column.


Current chart:


http://www.dead-fish.com/Files/130523%20-%20Puppy%20Weight%20Tracker.xlsx
 
Hi ,


Can you check your file here ?


http://www32.zippyshare.com/v/26300501/file.html


Basically you need to implement the CF on one column , and then copy it from that column to the others , one column at a time ; at least that's the way I have done it ; probably if the CF formula used the column number , you could apply it over the entire range at one go.


Narayan
 
Meh, never crossed my mind to apply a column at a time! A bit of a nuisance, but at least it makes spotting the little ones easier. Thank you Sir.


This is a phone picture from this morning fyi! they are still known by their coloured label names ;)


http://www.dead-fish.com/Pictures/Ruby%20&%20Pups%20at%206%20Days%20old.jpg
 
You must log in or register to reply here.
Back
Top