I am creating a request form in Excel that will capture the requirements for one or more products needed to meet the customers need. I have an index formula that will sort the ranking of each product available based on a score in an adjacent column but I need to be able to change the range that the formula pulls from based on the input. Is there a way to name a range within a formula based on a cell value?
Here is an example of what I'm working with.
Product Ball Bat Glove Helmet
Ball 3 2 1
Bat 3 1 2
Glove 2 1 3
Helmet 1 2 3
3 = high 1 = low
So in the example above I need to be able to ask the customer questions based on their need for each of the products. So if they ask for a "ball" from the drop-down menu on my form, I want to also ask them about their need for a bat and then a glove and then a helmet. My formula will work as long as the range is static. I would like to make the range change with the drop-down. So if they select "Glove" then my range should change to the Glove column and order questions based on highest need to lowest need.
For reference, the formula I am using right now is: {=index(ball,match(large(countif(ball,"<"&ball),row(1:1)),countif(ball,"<"&ball),0))}
Any help you can lend is very much appreciated!
Bookmarks