Autofill data from one tab to another under certain criteria

[dvp]

I enter customer details onto an excel 2010 spreadsheet, ie custome
name, date, description of order, amount of order etc. Sometimes, unde
certain criteria, the same or part of the same information needs to b
added to another tab, ie tab2, on the same spreadsheet. I wanted t
create an automated system to populate tab2, but cant figure out how.

I thought about creating another column in tab1, before all the custome
information, which had a dropdown list option for yes/no when asked th
question 'does it fit the criteria to add customer information to tab2'
This column would act as an alert, so that if i selected 'yes', then an
customer information i enter in tab1 would then populate into tab2. Bu
I dont know how to do this, can anyone help

 

[Gord Dibben]

Formulas or VBA?

Formulas................... in Sheet2 B2 enter

=IF(SHeet1!$A2="yes",Sheet1!B2,"") copy this across and down where
required

VBA would require event code to use the same logic to copy the info
without any formulas on Sheet2.

Post back if you want that................also provide more details on
"etc" and "some or part"


Gord

 

[dvp]

The formula works a treat, very simple, thanks. One more question, b
autofilling yes/no, is there a way where all the information on that ro
could be copied as well if the result is yes?

If i give you an example, below is sheet 1:

Required Supplier Description Amount
Yes Lets get branded 5000 shopping bags 3549
No Media Pool 200 t-shirts 5000
No Lets get stranded 1000 folders 3549

So that all populated cells in row 2 would be copied over to the sheet
as 'Yes' was used. Is this possible?



-

 

[Gord Dibben]

Select Sheet1 where you have the data as per your description below.

Assumes you have yes/no DV dropdowns A1 down to A100

Copy titles from B11 into A1:C1 on Sheet2

Select Sheet1..........right-click on sheettab and "View Code"

Copy/paste this code into that sheet module.

Option Compare Text 'makes text non-case sensitive
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100"
Dim srcerng, targrng As Range
n = Target.Row
Set srcerng = Range(Cells(n, "B"), Cells(n, "D"))
Set targrng = Sheets("Sheet2").Cells(Rows.Count, _
1).End(xlUp).Offset(1, 0)
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
On Error Goto endit
Application.EnableEvents = False
On Error Resume Next
If Target.Value = "yes" Then
srcerng.Copy Destination:=targrng
End If
End If
endit:
Application.EnableEvents = True
End Sub

Alt + q to return to Excel

Select "yes" in A2 and see B22 go to Sheet2


Gord