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.
| Mixes |
Common_Name |
Seeding_Rate |
| Mix_1 |
Partridge Pea |
12 |
| Mix_1 |
Illinois Bundleflower |
4 |
| Mix_1 |
False Sunflower |
8 |
| Mix_1 |
Purple Coneflower |
8 |
The closest formula I've found has been the INDEX formula. This is what I have currently:
=INDEX(Lists!B18:B25, SMALL(IF('Seeding Worksheet'!F20=Lists!A17:A25, ROW(Lists!A17:A25)-ROW(Lists!A17)+1), ROW(1:1)))
| Mix_1 |
Partridge Pea |
| |
#NUM! |
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?
