I'm pretty green with this complexity of excel, but I do have some programming background... any way... I'm a loan officer and I'm looking to make a spreadsheet to allow my prospects to play around with a calculator. I want the first sheet to have a few validation lists, and the second sheet to store my data.

On the first sheet I would like to have the first validation list be:

Interest Only? "yes/no"

Then underneath I would like the validation list to be one of two lists, the first being a list of interest only products (so a column on sheet 2 would have the interest only product names) and then the non-interest only products (another column on sheet 2 would have those product names).

Below that I would like a validation list that shows the interest rates that I am offering for the specific product picked in the cell above.

I have succesfully been able to do this latest step using offsets, and indirects, found here: (http://www.contextures.com/xlDataVal02.html). Basically in the validation list that would allow you to choose the interest rates, I have a =OFFSET(INDIRECT(A3),0,0,COUNTA(INDIRECT(A3&"ones")),1) Where A3 was the first validiation list letting me choose a product, so "Three.ARM" for instance. Then the column in sheet 2 referring to the rates for "Three.ARM" is named "Three.ARMones"

What I can't do which I would like is if the validation list where I choose a product, that if I could make the souce be "=IF(A1="Yes",=ARMInterestList,=ARMList)"

Also I was hoping there would be a way so that the cell next to the validation list that is the interest rate, if i could somehow make that cell refer to cell immedietly right adjacent to that cell with the interest rate on sheet2.

So on sheet 2, one column is the rates for a Three.ARM, the column to the right is the corresponding points. On sheet 1, I would want a valididation list for the interest only question (not sure how to get that involved), then a validitation list for the product type, once that is chose, then there would be a validation list for the interest rates offered on that product type... once that interest rate is chosen, then another cell would populate the cooresponding numerical "points" value that was in the adjacent cell to that specific interest rate reference on sheet 2.

Not sure if any of this makes sense.... thanks for bearing with me.