Manipulating Lists, Help Needed

Highlighted
Occasional Visitor

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?

0 Replies