lock cell whit first value

[cferoiu]

I try to find my problem, nothing.

if A[n]<>"" then B[n]=C[n-1] AND B[n]=LOCK

I undestand verry well english, but write is hard...
Thank You all for answer

 

[Ronald R. Dodge, Jr.]

If I understand you right, here is what you getting at:

If Cell A is not empty, then cell B of the same row is to equal Cell C of
the row above it.

Not only that, you also want to have the formula in the B column to be
locked.

First, Under the assumption you are starting at row 5, in cell B6, put in
the formula of:

=IF(A6="","",C5)

Copy that formula from B6, and paste it on down as far as you need to.

Select the entire worksheet, and then press Ctrl-1 (Format Cells)

Go to the "Protection" tab and checkmark "Locked" and "Hidden" (if you want
the formula to be hidden as well)

Now select the cells you want to have unlocked, and press Ctrl-1 again.

Uncheckmark the "Locked" option

Now protect the worksheet (may password protect it) via Tools, Protection,
Protect Worksheet (Alt, T, P, P) Yes, I do use that quite a bit with my
work.

Granted, the protection stuff in Excel won't protect against the determined
people, but it will at least protect from the casual people (which are most
people).

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[cferoiu]

Is not a solution for me.
I think I need some VBA solution.
The column C change often, in cell B I need lock FIRST value under formula
posted above.

Thank you for your time.

 

[cferoiu]

I try google translate:
I have not created the column C based on its column B to create according to
my post above.
This is a dynamic table, the values in column C change regularly, and in
column B we need the first values that appear in girdle under condition C
posted first. I do not want to exchange all the worksheet protection.


And another thing: it can make a macro simply to observe that condition:

if A [n ]<>"" then B [n] = n??


Thanks for your time

 

[Ronald R. Dodge, Jr.]

If you talking about arrays, that can be done as well.

If using the worksheet method, you can use a column as an array of values
and if need be setup a 2 column table with the first column being the key
value and the second column being the actual value for that particular key.

Your second away would be setup the same way. Once you have your 2
different 2 column tables setup, you can then use the VLOOKUP function to
get the values

Now for assigning values to an array, it may require the use of VBA coding
depending on the set of circumstances, which case, if you know VBA coding
well enough, then you can use class regular objects and class collection
objects to assign (Set/Let) and get (Get) values. However, if you are new
to VBA coding, you can get by with just defining an array variable and start
learning how to use variables within VBA.

Another option is to look into how to use User Defined Functions (UDF) which
also is setup within VBA. There are limitations to using UDF, but they in
itself can also be very powerful.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[cferoiu]

I find my problem; normaly with VBA code:

Private Sub Worksheet_Change(ByVal target As Range)
' Test that the modified cell is in the column A.
If target.Column = 1 Then
' Get the row of the modified cell.
rowOfActiveCell = target.Row

' Update the cells on the column B from the same row, only if
there's no formula yet filled.
If (Cells(rowOfActiveCell, 2).Value = "") Then
Cells(rowOfActiveCell, 2).Value = Cells(rowOfActiveCell - 1,
3).Value
End If

' if A[n] deleted then B[n] deleted
If (Cells(rowOfActiveCell, 1).Value = "") Then
Cells(rowOfActiveCell, 2).Value = ""
End If

' if A[n]<>"" then D[n]=n
Cells(rowOfActiveCell, 4).Value = rowOfActiveCell
End If
End Sub