Copy row of data if column 1 is a certain number

woodstock1984 · Mar 27th 2017

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?!

**NBVC** · Mar 27th 2017

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....

In A3 add counter formula:

[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.

Copy row of data if column 1 is a certain number

Participate now!

Share