Force List Cell Value into Another Cell

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have a range of values on Sheet2. I then defined a range name for
values, e.g. "Description."

On Sheet1, I then used "=Description" under the Data Validation |List menu
in column A.

I now can select cell A1:A50 and get all the values listed in the
"Description" range on Sheet2... so far so good...



Here's now what I need to achieve:
When I click on A1 and get the values of the descriptions, instead of
"entering" them into A1, I want them actually entered in B1.

or

When I click on A1 and the the values of the descriptions, I want them
entered into A1 & B1. I later then can delete the values from A1 if B1 if
populated.



Any suggestions how to do this w/ the list?

Thanks,
Tom
 
Hi
an easy solution would be the formula
=IF(A1<>"",A1,"")
in cell B1
BUT this does not remove the entry from cell A1 nor is it
possible to delete cell A1
If you require this you'll need VBA (an event procedure)
to achieve this goal.
 
Frank:

I tried this solution but I realize that this won't work for me... here's
why...

The problem is that I'm inserting rows into an existing matrix. The
inserted row keeps the functionality to select from the drop-down box. I
now want to select the cell of the newly insert row in column A -- e.g. A15
and update B15 with A15.

Again, I tried to use your function, but they get deleted if I delete the
value in A15. Actually I was using a "helper column" (column C) that stores
function for row 15. But again, selecting/deselecting values in a given row
will delete the function in column C as well.

So, I'm back to square one... is there a chance to force to update a cell
based on the selected value from a list in another cell?

Thanks,
Tom
 
sounds like you need a vba solution for that. why don't you try this:


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Row = 1 Then
Range("A1").Copy
Range("B1").PasteSpecial (xlPasteValues)
End If
Application.CutCopyMode = False

End Sub
--------------------


in sheet desired, right click tab (sheet name) -> view code. paste in
right window. will work for A1 only and will need to be adjusted if
you want the entire column A. if that is what's desired, then on the
first line of code (If Target.Column...) delete everything after the
'AND', leaving the 'Then' looking like this:


Code:
 
Thanks for the VBA... it's almost perfect!

I modified the code per your instructions...

&&&&&&&&&&&&&&&&&&&&&&&&
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
Range("A2:A150").Copy
Range("B2:B150").PasteSpecial (xlPasteValues)
End If
Application.CutCopyMode = False

End Sub
&&&&&&&&&&&&&&&&&&&&&&&&


Here's the problem now... currently it takes the range A2:A150 and pastes
all values
into B2:B150.

Column B has some existing values which cannot be overwritten (currently
with blank values).

Here's some example... keep in mind that column A pulls from a list and
currently no selections have been made:

BEFORE:
Row 1 A B
Row 2 X
Row 3
Row 4 Y
Row 5
Row 6 Z

AFTER:
Row 1 A B
Row 2
Row 3 a a
Row 4
Row 5 b b
Row 6


PROBLEM:

Cell B2 lost value "X"
Cell B4 lost value "Y"
Cell B6 lost value "Z"


What it should look like:
Row 1 A B
Row 2 X
Row 3 a a
Row 4 Y
Row 5 b b
Row 6 Z


Any suggestions how to fix that?

Thanks again in advance,
Tom
 
Back
Top