avoiding circular reference

Little help please.


Problem:

I am trying to have the ability to have a group of cells change in response to one of them being modified.

Situation:

I have a widget that I want to get prices from different vendors for. I want to note all the different prices and then alert my program which one it is to use.

Setup:

A B C D E F G H
1 Part | Price 1 | Alert | Price 2 | Alert | Price 3 | Alert | Purchase Price
2 Widget | $1.25 | Y | $1.37 | N | $1.02 | N | $1.25



My Approach:

I was attempting to set a data validation for columns C, E, & G using two options, Y or N (Yes or No). Then I wanted to put a formula in each of those columns that would say if one of the other columns was selected with a Y then they would automatically return an N. Then cell H2 would then look for the Y and use the number preceding it.

Note:

I may not necessarily choose the lowest price. I want the user to be able to select which vendor the product was purchased from.

why not use radio buttons? group 3 together and link to another cell say aa1 as each is selected that cell will change to 1,2 or 3 then use in h2=choose(aa1,b2,d2,f2)
or
use 4 buttons and use the 4th as a clear button =choose(aa1,b2,d2,f2,"") however if you wanted that on loads of rows it would be very maesy to set up!
i suppose it could be coded

Thanks, that helps but unfortunately I do have a long list of items so it will be cumbersome.

But you did give me an idea on how to tackle.

I created a new column at the beginning that is a data validation. It asks for either Price1, Price 2, Price 3, etc. Then I added a column at the end that creates a number between 1 and 29 for the data validation answer. Then my current price chooses the price based on the last column. I can then drag all the formulas down the list for all my parts.


I'm still thinking there is a smoother way to handle this but you did help solve the problem.

I'd be curious if any others have more elegant solutions (not to detract from yours, though).

I will leave this post unSOLVED for a little while to see if I get other answers.