Before I throw my question out there, let me explain the situation.
I work for a chicken farmer who has 3 barns with chickens. Every day the eggs have to be collected and sorted (good, weak or cracked, dirty, too heavy or too light). For each barn, the quantity and quality of the eggs have to be monitored, I chose to do that with Excel. My current layout looks like this, with the data shown per week number: (see attachment).
The top row displays the week numbers, the rows below that are data types, like amount of good eggs, percentage of bad eggs, dirty eggs etc. The barns are color-coded. The button in the top-left corner is assigned a macro that gathers all the data per week. I would like a graph that automatically updates itself once data from a new week is added. I also want to compare certain data types, for example average daily production, egg quality and water intake of the chickens, between each barn. In the past I used the Name Manager to make dynamic ranges, but back then I only had 3 data types per barn, whereas I currently keep track of 10 data types per barn and that number is likely to increase in the future, which makes defining the dynamic ranges rather burdensome.
So I would like some advice/tips on how to do this. Any ideas/suggestions are welcome. NB: The Excel file is in Dutch, but that shouldn't be a huge problem.
Bookmarks