Excel - Macro to populate cell value from another sheet based upon the data validation list selection

Asked By Hasan on 12-Nov-09 03:56 AM
Hi,

How do i write a macro to populate cell value from another sheet based
upon the data validation list selection




Simon Lloyd replied on 12-Nov-09 04:20 AM
You have not explained populate what cell with what data from where,
however here is something you can work with, i have data validation in G1
and my lookup table in M1:N12 and i want my result in A1


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cVal
If Target.Address <> "$G$1" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
cVal = Application.WorksheetFunction.VLookup(Target, Range("M1:N12"), 2, 0)
Range("A1") = cVal
End Sub
--------------------

*How to Save a Worksheet Event Macro*
1. *Copy* the macro above placing the cursor to the left of the
code box hold the *CTRL & Left Click,* then *Right Click* selected code
and *Copy.*
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*




Hasan;556077 Wrote:


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=153354

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]
Hasan replied to Simon Lloyd on 13-Nov-09 06:24 AM
), 2, 0)
=3D1
354

I have a data validation list in Sheet1.A1:A2000 where source of data
validation list is Sheet3.A1:A2000.

Depending upon the selection from the validation list in Sheet1 I want
the other columns(B,D,F,G) in Sheet1 to populate its corresponding
values in Sheet3.Column C,F,G,M
Simon Lloyd replied to Hasan on 13-Nov-09 02:10 AM
Hasan, why have 2000 cells with data validation in sheet 1 and have the
list in sheet 3, surely it would be better just to have the list in
sheet 1 too?

Hasan;557248 Wrote:


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=153354

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]
Hasan replied to Simon Lloyd on 16-Nov-09 02:41 PM
n sheet 3, surely it would be better just to have thelistin
-
=3D1
354
e quoted text -

I am trying to create an automated procedure, where in the User

1. Exports the data into an excel file(Say Sheet3)
2. Selects the value in Sheet1.Column A (which is data validation list
from sheet3.Column A)
3. Depending upon the criteria in below code it updates the data in
relavent sheet else gives error.
4. I need to further automize the things, like Depending upon the
selection from the validation list in Sheet1 I want the other columns
Hasan replied to Hasan on 23-Nov-09 03:50 PM
tin sheet 3, surely it would be better just to have thelistin
G1
b*
---
-
id=3D1
53354
ide quoted text -
David replied to Simon Lloyd on 14-Feb-10 11:59 PM
THanks Simon...this helped me out Greatly!