Hi, ortizmo. I'm trying to get a bigger-picture idea of what's going on. A few questions:

1) You're trying to find the minimum price, I take it?

2) What's the significance of rows 5 and 21? Are the four tables all on the same worksheet, I'm guessing?

3) Isn't it possible that ~more~ than one table will have a part not available? Seems to me you're trying to make sure your formula works even when one value isn't available, but not considering that there may be more.

4) What is the value in the price column when the Unavailable box is checked? Does it stay the same as before?



5) There may a formula that'll work for this, but it seems complex enough that it may pay you to write your own worksheet function. And you did, after all, post this question in the VBA ~programming~ forum, so I suppose you've considered that option, even though you don't mention it?

#1/#2:
Ok, so let me explain...I have 5 tables in 1 spreadsheet. 4 of them hold prices for different online stores. The 5th one looks through all 4 and MIN is returned with formulas that change the item description based on the MIN found and also some comparison formulas to see the savings between them.

The item I'm testing this on is a CPU price...the price for that item (which repeats in the other tables) are: D5, L5, D21, L21 respectively.

My checkbox resides on the outside of each tables with a cell in FALSE which changes to TRUE when the check is ON. The cells for TRUE/FALSE are: H5, P5, H21, P21 respectively. And a MIN of all PRICE cells, if = FALSE.

#3: On this question, you are correct...the thing is I'm testing the formula just for a single item (CPU). So yes, when D5 = TRUE then ignore it and continue with the others that are = FALSE (available).

#4: The item still shows the price on its table, but the 5th table where this formula will reside will try to ignore it, hence, IF(D5=TRUE...) and not take the price into account.

#5: I thought this area was for complicated formulas...not paying for programming...my apologies, that was not my intention.


My formula causes trouble because if D5=True then MIN the rest...and the problem is what if I have others that are also not available?! Then I cannot write the next part because I already say ignore D5 go with the rest...but the third one is looking to the D5 included in the MIN which I already say ignore it...then it still shows up. I'm trying to find a way to take into account all possible combinations of not available into consideration. Not sure if I made the explanation even worse now.