# Copy row of data if column 1 is a certain number

• forum.ozgrid.com/index.php?attachment/71980/I have a spreadsheet that I need to show certain rows of data automatically from the master sheet on to 5 different tabs if column A has a number 1-5

So on the master data tab all the information is entered, I need
all row data that has a '1' in column A to be copied on to tab TW
all row data that has a '2' in column A to be copied on to tab JoS
all row data that has a '3' in column A to be copied on to tab JoM
all row data that has a '4' in column A to be copied on to tab JaS
all row data that has a '5' in column A to be copied on to tab KL

I hope this makes sense?!

## Files

• Re: Copy row of data if column 1 is a certain number

Using formulas....

For example, in TW sheet, for first table of Master sheet....

[COLOR="#0000FF"]=COUNTIF('Master Data'!\$A\$4:\$A\$53,1)[/COLOR] change 1 to 2, 3, 4, etc in other sheets.

In A5 enter formula:

[COLOR="#0000FF"]=IF(ROWS(\$A\$5:\$A5)>\$A\$3,"",1) change 1 to 2, 3, 4, etc in other sheets.[/COLOR]

copied down.

In B5, enter this Array* formula:

[COLOR="#0000FF"]=IF(\$A5="","",INDEX('Master Data'!\$B\$4:\$B\$53,SMALL(IF('Master Data'!\$A\$4:\$A\$53=\$A5,ROW('Master Data'!\$B\$4:\$B\$53)-MIN(ROW('Master Data'!\$B\$4:\$B\$53))+1),COUNTIF(\$A\$5:\$A5,\$A5))))[/COLOR]

copied down

[arf]*[/arf]

In C5 enter formula:

[COLOR="#0000FF"]=IF(\$A5="","",INDEX('Master Data'!C\$4:C\$53,MATCH(\$B5,'Master Data'!\$B\$4:\$B\$53,0)))[/COLOR]

copied down and across the rest of the table.

Repeat these for each table on each tab.

Where there is a will there are many ways. Finding one that works for you is the challenge!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!