Autofill data from one tab to another under certain criteria

[dvp]

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?

 

[Derek Brown]

Here is a way that you can do this:

On Sheet 1, add a new Column A, give it the heading "Required" and in the cells below enter either "Yes" or "No" according to whether those rows are required or not.

On Sheet2:
Copy your headings from Sheet1 to Sheet2 and then replace the heading "Redquired" with the numeric value 1.

A2 - Enter the formula:
=IFERROR(MATCH("Yes",OFFSET(Sheet1!$A$2,0,$A$1-1,COUNTA(Sheet1!$A:$A),1),0),"")

A3 - Enter the formula:
=IFERROR(MATCH("Yes",OFFSET(Sheet1!$A$2,A2,0,COUNTA(Sheet1!$A:$A)-A2,1),0)+A2,"")

Copy-down the A3 formula as far as you need to match the rows on Sheet1.

B2 - Enter the formula:
=IFERROR(INDEX(Sheet1!$A$2:$F$20,$A2,COLUMN()),"")
but change the $F$20 in the above formula to the last column and row in your data on Sheet1.

Copy the formula in B2 across the other columns and then down for a many rows that you have in Column A.

And that's it - it looks complicated but if you follow the example step by step it will not be difficult to set up.

If you have any blank cells in your dataset (Sheet1), you will probably get the value 0 (zero) on Sheet2. To overcome this, you will need to test for the cell having zero length. You will need to format Sheet2 columns to match those on Sheet1.

Column A on Sheet2 can be now be hidden.

 

[dvp]

I didnt understand your method, i tried it but couldnt get it to work. Apologies for being so thick, perhaps if i give you an example?

<TABLE style="WIDTH: 264pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=351><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20 width=64>Required</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=105>Supplier</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 94pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=125>Description</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 43pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=57>Amount</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Lets get branded</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>5000 shopping bags</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right>3549</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Media Pool</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>200 t-shirts</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right>5000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Folder we go</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>1000 folders</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right>3549</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Pentastic</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>650 pens</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right>5000</TD></TR></TBODY></TABLE>

The above is data on sheet 1, so what i need to happen is for rows 2 and 5 (which show Yes as a result) to show on sheet 2 ie see below:

<TABLE style="WIDTH: 264pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=351><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20 width=64>Required</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=105>Supplier</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 94pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=125>Description</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 43pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=57>Amount</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Lets get branded</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>5000 shopping bags</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right>3549</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Pentastic</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>650 pens</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right>5000</TD></TR></TBODY></TABLE>

Is this possible?

 

[Derek Brown]

I would expect the given formulas to work for the example that you have given.
Could you post the formulas that you have in Cells A2, A3 and B2 on Sheet 2 please and I will check them for you.
(I may not get a chance to look at them for 2-3 hours though.)

 

[dvp]

I've managed to get it to work, an error on my part. Thanks very much for your help, its much appreciated!

 

[Derek Brown]

Thank you for letting me know that all is now OK - pleased that it is working.

 

[dvp]

It really works a treat, thank you. In addition, is there a way where you can select which columns to autofill from sheet 1 to sheet 2? Currently, the formulas populate everything that is selected from each row. Such as in the example below, is there a way to just choose B and D columns for sheet 2, ie Supplier and Amount?

 

[Derek Brown]

No problem.
On Sheet1 you need to give a 'name' to each heading required on Sheet2. In the example that you gave with "Supplier" in B1, select that cell and give it a name using the 'Name Box' above the A-column. I would give it the name "SupplierCol" to make it meaningful - note that you cannot use spaces and there are some characters that cannot be used.

Then select cell D1 and give it the name "AccountCol".

On Sheet2 you need to change the formulas as follows:
Assuming that column B is the Supplier column:
Change the formula in B2 from
=IFERROR(INDEX(Sheet1!$A$2:$F$20,$A2,COLUMN()),"")
to
=IFERROR(INDEX(Sheet1!$A$2:$F$20,$A2,COLUMN(SupplierCol)),"")
and copy it down the the rows below.

Assuming that column C is now your Amount column:
Change the formula in B2 from
=IFERROR(INDEX(Sheet1!$A$2:$F$20,$A2,COLUMN()),"")
to
=IFERROR(INDEX(Sheet1!$A$2:$F$20,$A2,COLUMN(AccountCol)),"")

That is all you need to do - change the "COLUMN()" part of the formula. You will also find that you can place the columns in any order that you want - for example, Amount in column B and Supplier in column C.

 

[james_mcdonald]

Here is a way that you can do this:

On Sheet 1, add a new Column A, give it the heading "Required" and in the cells below enter either "Yes" or "No" according to whether those rows are required or not.

On Sheet2:
Copy your headings from Sheet1 to Sheet2 and then replace the heading "Redquired" with the numeric value 1.

A2 - Enter the formula:
=IFERROR(MATCH("Yes",OFFSET(Sheet1!$A$2,0,$A$1-1,COUNTA(Sheet1!$A:$A),1),0),"")

A3 - Enter the formula:
=IFERROR(MATCH("Yes",OFFSET(Sheet1!$A$2,A2,0,COUNTA(Sheet1!$A:$A)-A2,1),0)+A2,"")

Copy-down the A3 formula as far as you need to match the rows on Sheet1.

B2 - Enter the formula:
=IFERROR(INDEX(Sheet1!$A$2:$F$20,$A2,COLUMN()),"")
but change the $F$20 in the above formula to the last column and row in your data on Sheet1.

Copy the formula in B2 across the other columns and then down for a many rows that you have in Column A.

And that's it - it looks complicated but if you follow the example step by step it will not be difficult to set up.

If you have any blank cells in your dataset (Sheet1), you will probably get the value 0 (zero) on Sheet2. To overcome this, you will need to test for the cell having zero length. You will need to format Sheet2 columns to match those on Sheet1.

Column A on Sheet2 can be now be hidden.

I know this is an old post, but I am trying to accomplish this myself. I cannot seem to get this to work. Any help?

 

[Derek Brown]

I can see no reason why this would not work with the newer versions of Excel.
Are you getting any formula errors reported?