Autofill data from one tab to another under certain criteria
 

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

I thought about creating another column in tab1, before all the customer information, which had a dropdown list option for yes/no when asked the 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 any customer information i enter in tab1 would then populate into tab2. But I dont know how to do this, can anyone help?

Autofill data from one tab to another under certain criteria
 

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

On Thu, 5 Jan 2012 12:57:01 +0000, dvp
wrote:


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

I thought about creating another column in tab1, before all the customer
information, which had a dropdown list option for yes/no when asked the
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 any
customer information i enter in tab1 would then populate into tab2. But
I dont know how to do this, can anyone help?

The formula works a treat, very simple, thanks. One more question, by autofilling yes/no, is there a way where all the information on that row 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 2 as 'Yes' was used. Is this possible?



On Thu, 5 Jan 2012 12:57:01 +0000, dvp
wrote:


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

I thought about creating another column in tab1, before all the customer
information, which had a dropdown list option for yes/no when asked the
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 any
customer information i enter in tab1 would then populate into tab2. But
I dont know how to do this, can anyone help?[/quote]

Autofill data from one tab to another under certain criteria
 

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 B1:D1 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 B2:D2 go to Sheet2


Gord

On Wed, 11 Jan 2012 16:04:16 +0000, dvp
wrote:


The formula works a treat, very simple, thanks. One more question, by
autofilling yes/no, is there a way where all the information on that row
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 2
as 'Yes' was used. Is this possible?



On Thu, 5 Jan 2012 12:57:01 +0000, dvp
wrote:
-

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

I thought about creating another column in tab1, before all the
customer
information, which had a dropdown list option for yes/no when asked
the
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
any
customer information i enter in tab1 would then populate into tab2.
But
I dont know how to do this, can anyone help?-