I am attempting to revitalize an outdated spreadsheet that I use for work. This spreadsheet is supposed to allow you to select a particular standardized mix of forb seed from a drop down, and then auto-populate the correlating information into the adjacent cells. Over the years and saves and copies, this original worksheet no longer does what it was designed to do. I am really struggling to figure out the appropriate formula or combination of formulas to sort this out so it functions again.
So this is what I got. Here is an example of just 1 of the mixes. I have a total of 10. The list of mixes is on the Lists worksheet, which is separate from where I'm working with the formulas.
The closest formula I've found has been the INDEX formula. This is what I have currently:
What I get when I use this formula: I get the first forb listed in my Mix_1 list. When I copy this formula to the cell below, I get a #NUM! response. Also, I haven't even begun to tackle the seeding rate value in with this yet. I'm not even sure how to wrap my head around doing that.
I cannot figure out how to make Excel auto-populate a multi-variant list vertically and horizontally from one input value. If I have to make a separate formula for the seeding rates as well I can. But if it's possible to get it all in one go I'd like to try. Help?