Help Req

[Nabeel]

hello All
plz find attached want to when i select in yellow cell "full day" formula in red cell will be work and when i select half day in red cell become half day.
and there is four option of check casual,sick,CPL & annual only one time one check will mark..put changes & plz also to guide how to do.
BR

 

[eibi]

Nabeel,

See attached. Is this what you wanted?

Here are the changes I made:

1. Adjusted the formula in the red cell (L6)

=IF(K7="Half Day",0.5,J6-G6+1)
​

2. Applied conditional formatting to the red cell (L6) to show a decimal (0.5) when "Half Day" is selected and a whole number with a leading zero (02) when "Full Day" is selected.

3. Changed the check boxes (which allow multiple selections) to radio buttons (which allow only one selection) -- and linked the radio buttons to cell P2.
​

Is this what you were wanting?

All best.

 

[Nabeel]

dear eibi
thnx for helping...i do not want 0.5 in digit i want "half day" in written..
BR

 

[eibi]

Then paste this formula in Cell L6:

=IF(K7="Half Day","Half Day",J6-G6+1)

or

=IF(K7="Half Day",K7,J6-G6+1)

(And delete the custom formatting rules.)

attached.

 

[Nabeel]

Thnx a lot dear eibi

 

[Luke M]

kudos to you @eibi , you are the better mind reader.

 

[Nabeel]

hi there ..hows u
one more little help req...in yellow cell there is drop down list..i want to write "plz Select" after selection required thing shows if nothing there than Plz select shows......it should be no editable..plz also guide how
BR

 

[eibi]

Nabeel,

Putting "cue text" into a blank cell is fairly tricky -- I've tried lots of ways, but never been satisfied.

You can do it with VBA, but why don't you just put the Please Select: text in cell J8? -- as you've already done with the prompt in cell D7.

 

[Nabeel]

dear eibi
can i write please select in yellow cell.

 

[eibi]

Nabeel, my friend -- you are quite a handful...

You are asking for something tricky. I will give you my best solution:

1. Turn off data validation in the yellow cell.

2. Type Please Select in the yellow cell.

3. Turn the data validation in the yellow cell back on.​

See attached.

What does this do? It allows your cell to display Please Select

However, after a choice is selected (Full Day,Half Day) and then deleted, the original prompt (Please Select) will not reappear.

If you are building an Excel template, this should work for your purposes. Each time the new form is opened, Please Select will show until a selection is made.

Another in-cell solution would be to add a third option to the dropdown box:

Please Select, Full Day, Half Day​

I assume though from your past posts that this isn't the solution you will prefer.

If you want to toggle between a selection and a "blank" cell that shows please select, you are asking for a VBA solution.

All best.

 

[Nabeel]

ok..thnx for helped..