input or copy data
 

Hello,

I'm would like to have the data entered into one cell be copied to
another cell so the value is = that value. I hope I can explain this:

Cell A1= 100
Cell B1=needs to be 100 or greater, but i want someone to entered a
greater number if they would like. if they try to enter a number less
than A1 then they get the value of A1 again or an error box telling
them of their mistake. I'm afraid this will require some type of basic
programming? If it requires programming where does it go? in the B1?

Thanks in advance for your help!! mitch

input or copy data
 

I think Data Validation might suit your needs.

Using your example....
Select cell B1
From the Excel main menu:
<data<validation
Allow: Custom
Formula: =A1

Using the defaults, that will prevent B1 entries of less than the A1 value.
Explore the other Data Validation tabs for more options.
Post back with any other questions.

Also, check Debra Dalgleish's website for more instructions:
http://www.contextures.com/xlDataVal01.html

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"lawdoggy" wrote:

Hello,

I'm would like to have the data entered into one cell be copied to
another cell so the value is = that value. I hope I can explain this:

Cell A1= 100
Cell B1=needs to be 100 or greater, but i want someone to entered a
greater number if they would like. if they try to enter a number less
than A1 then they get the value of A1 again or an error box telling
them of their mistake. I'm afraid this will require some type of basic
programming? If it requires programming where does it go? in the B1?

Thanks in advance for your help!! mitch

input or copy data
 

Hi Ron,

Thank you for helping out. It did work to a certain extent but if
someone went back later and re-enters data into A1 after then have
input data into B1 then it doesn't. I think I will probably need a
little programming lingo to always check the value of A1 to make sure
it's <= B1. If you have any other suggestions I would be glad to give
them a try. Thank you for your time! mitch

input or copy data
 

You're right about needing a bit of VBA coding to reset the Col_B list when
the Col_A list item is changed.

Here's the code to be pasted into the sheet module for the worksheet with
the Data Validation:
(To see that module, Right-Click on the sheet tab and select View Code)

'---------Start of Code------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngAllParentCells As Range
Dim rngDepCells As Range
Dim rngCell As Range

Set rngAllParentCells = Range("A1:A10")
Set rngDepCells = Intersect(Target, rngAllParentCells)

If Not rngDepCells Is Nothing Then
For Each rngCell In rngDepCells.Cells
rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents
Next rngCell
End If

Set rngAllParentCells = Nothing
Set rngDepCells = Nothing
Set rngCell = Nothing

End Sub
'---------End of Code------------

Note: that code works for data validation lists in A1:A10 with corresponding
dependent DV lists in B1:B10. The code recognizes a value change in the
Col_A DV list and OFFSETs one column to the right to clear the contents of
the Col_B cell.

You can change the A1:A10 ref if the DV lists are elsewhere.
You can also change the OFFSET if the dependent list is more than 1 column
to the right.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"lawdoggy" wrote:

Hi Ron,

Thank you for helping out. It did work to a certain extent but if
someone went back later and re-enters data into A1 after then have
input data into B1 then it doesn't. I think I will probably need a
little programming lingo to always check the value of A1 to make sure
it's <= B1. If you have any other suggestions I would be glad to give
them a try. Thank you for your time! mitch