I would like to either populate a cell value from direct key input or have the cell value derived from a formula.

[delos]

I would like to either populate a cell value from direct key input or have the cell value derived from a formula.

D6 is the first OFF on image. I have tried using this formula i got from another forum and modified it for what you see above, but when i use letters like OFF it doesn't work can someone help me with this.
Also K6 is a hidden cell with the formula in it, and i refer to it in L6 as =K6. L6 is the cell with answer and ability to over ride with key input. The hours and days off have data validation with drop downs on them to make things simpler. I would like to apply the vba formula below to all of L column starting on L6 all the way down so that when the formula in K6 which is =COUNTIF(D6:J6,">-1")*8, gives the answer using formula or ability to manually input another #.
<code>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("D6:J6")) Is Nothing Then
If WorksheetFunction.Count(Range("D6:J6")) = 7 Then
Range("L6").Formula = "=K6"
End If
End If

End Sub

I don't know how to program so I don't think above vba is right.
Any help please!
</code>

 

[delos]

That's great. I assume Peters solution worked for you.

Yes he did can you help with the remaining problem of adding the SL sick leave and AL annual leave, overtime and training also. It would be similar to what Peter did. If the hours is changed to text like S/L it will add 8hrs to sick leave and same goes with A/L. If I put A/L instead of hours the A/L column will do a countif for A/L times 8 and give the right hours. I have the formula already worked out I just need cells O,Q,S, and U, to keep formulas of hidden cells.
Thanks again

 

[Peter_SSs]

I have the formula already worked out I just need cells O,Q,S, and U, to keep formulas of hidden cells.

If you have the formulas in place in columns N, P, R & T (as well as K) then just make this change in the code I previously posted and it should update columns L, O, Q S & U with formulas referencing your hidden ones.

<del>Cells(c.Row, "L").FormulaR1C1 = "=RC[-1]"</del>
Range(Replace("L#,O#,Q#,S#,U#", "#", c.Row)).FormulaR1C1 = "=RC[-1]"

 

[delos]

Works wonderfully. This is why I went with formulas in cells and hid them, it makes it more flexible to where I don't have to update the VBA code when I change the formulas around so thank you for not straying to far from my design.

I have 2 questions though. I would like to learn VBA. Is there a book that you can recommend to read? I think it would come in handy and possibilities are limitless!

The other question is since I have put the codes in I can't hit the back arrow or forward they are never darkened so I can't undo or redo. Is this fixable or a side effect of putting in VBA since this is the first excel sheet I have ever put one in?

Thanks in advance.

 

[Peter_SSs]

Works wonderfully. This is why I went with formulas in cells and hid them, it makes it more flexible to where I don't have to update the VBA code when I change the formulas around so thank you for not straying to far from my design.

You are very welcome. Glad it was what you wanted.

I would like to learn VBA. Is there a book that you can recommend to read?

I am largely self-taught from the forum and trial & error so don't have anything specific to recommend but you might find something useful in this extensive list prepared by another forum member.

The other question is since I have put the codes in I can't hit the back arrow or forward they are never darkened so I can't undo or redo. Is this fixable or a side effect of putting in VBA since this is the first excel sheet I have ever put one in?

Macros basically destroy Excel's built-in 'Undo' and there isn't a simple work-around that I am aware of. Depending on what the macro does and the user's requirements are, most work-arounds that I have seen involve having the original macro store the existing information in another worksheet or in memory before doing whatever it was designed to do and then having a second macro that can be run to put that original information back.

Another option to avoid accidents is to have the original macro pop up a message box like "Are you sure?" before it actually does what it was designed to do so the process can be stopped at that point. Of course if you are "sure", having to answer that question every time can become annoying.

 

[delos]

Thank you for everything. Saved that link to favorites!