IF statement with variable outcome

[Jeff Rich]

1st Question:
I am building an estimating spreadsheet and have a situation that has multiple ways of fabrication. How would I write IF($G$12="Lago Vista","Tile","XXX"). In this case "XXX" is the material in a different cell. That material can change dependent on other previous resolved factors. There are 6 multiple material choices for Cell "XXX".

2nd question:
Can I have a drop down list and an if statement in the same cell?

[JBeaucaire]

1) You replace the "XXX" with the cell reference. I apologize for the simplistic answer, I just don't see enough information to posit a more complex answer.

IF($G$12="Lago Vista","Tile", $C$1)


2) Yes, you can add Data Validation to a cell that also has a formula in it. I use this technique frequently in ORDER FORMS where I want some automation to occur the first time a row is filled out, but the user can override that automation (formula) with a drop down that offers other features.

However, as soon as the user makes a selection from that drop down, the formula is erased forever in that cell. This is fine in the case of my ORDER FORM above, order forms (templates) are usually filled out once, so a new order would open a new pristine order form and the formulas are intact at the beginning.

If your cells are not part of a template, and you need for those formulas to possibly be reinstated somehow, that would most likely require VBA... a macro that watches for certain cells to be changed to blank and it reinserts the original formula(s) in that instance.

[Andy Pope]

Formula in I6 would be
=IF(F2="Lago Vista","Tile",I5)

You can use data validation in I5

No you can not have formula and dropdown.

[Jeff Rich]

Thank you Jerry.
The simplest answer is exactly what I needed. I kept trying to write is about that basic but to no luck. I was missing or adding 1 item. LOL Thank you.
I use a master sheet for each project, so disappearance of the formula if over written is okay. But I do like that Macro idea. I have no idea as to write or use one though. That will have to wait for another day.

Thank you again for the help

[Jeff Rich]

Thank you Andy

[Jeff Rich]

Jerry-
How would I write the data validation with a formula and a list in the same cell?

[Jeff Rich]

How would I write the formula and still have a drop down list?

[JBeaucaire]

You have to enter your formula into the cell first, preferably with a blank initial result.

Then apply your Data Validation list settings to the cell itself to activate the drop down functionality.

As long as you do not use the Drop Down, the formula can display any result.

[Jeff Rich]

Okay. Makes since. Thanks Again. You have been very helpful.

[JBeaucaire]

Note, get that formula 100% first, it is tricky to edit that formula at all once the DV is active on the cell.