Conditional formatting of a range of cells

[camera1]

Hi All,

Totally new here and not very experienced with Excel so please be patient.

Question:

By selecting a value from a drop down list, I want to be able to change a row of five cells from a blank line to the line with formulas listed below and vice versa, depending on the value set by the drop down list. (The first cell is a text label, the second cell is a user input variable, the third cell is a sum formula, the forth cell conditional constant and the fifth cell is a sum formula.)

<table border="0" cellpadding="0" cellspacing="0" width="481"><colgroup><col width="189"><col width="93"><col width="85"><col width="56"><col width="58"></colgroup><tbody><tr height="21"> <td class="xl63" style="height:15.75pt;width:142pt" height="21" width="189">Fuel %</td> <td class="xl66" style="width:70pt" width="93">50</td> <td class="xl64" style="border-left:none;width:64pt" width="85">213.30</td> <td class="xl64" style="border-left:none;width:42pt" width="56">3.48</td> <td class="xl65" style="width:44pt" width="58">741.22</td> </tr></tbody></table>
Any help would be greatly appreciated.

 

[taurean]

Welcome to the board Camera1.

Please explain:
1. What will you be selecting from dropdown list (I'm supposing Data Validation)?
2. How the rest (items to be populated automatically) is associated with this selected value?

 

[camera1]

Hi taurean,

The drop down list is a list of different types of helicopter, some have single fuel tanks and the rest have twin tanks.

I want to be able to show the fuel state of the single or the twin engine variants. Column 1 is a text label, column 2 is the fuel quantity as a percentage, column three is the quantity in litres, column 4 is the longitudinal arm of the tank and column 5 is the resultant moment.

By selecting a single engined helicopter I would have a single line for the fuel state, if a twin engined helicopter is selected from the drop down list the there would be two lines, one for each fuel tank.

Hope this makes sense.

By inputing the fuel state as a percentage in column 2, the sheet will calculate the moment.

 

[taurean]

If you want Excel to insert a row automatically when Twin cylinders are selected then you will need VBA.

If you intend to copy down the formula then it will be something like:
Suppose you have Single / Twin cylinder option in cell B2 then in cell C2 you can write formula given below.

=IF(B2="","",[COLOR=red]"YOUR FORMULA GOES HERE!"[/COLOR])

When the cell B2 is blank all the cells with above formula will appear blank.
As soon as you select an option the case False will execute which will calculate based on your input.