**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have a mastersheet with all of my data on it, in the form of a table, which needs to have more data added in the future. This mastersheet also will have the filters applied meaning the data will be moving from its origin. I am creating multiple other sheets within the workbook that will display specific rows from the mastersheet and automatically update when the mastersheet is altered. I have tried many ways to make this happen but it always seems to fail once I apply a filter and the data is not in its original cell. It appears as if I can only link the sheets to a specific cell on the mastersheet rather than to the relevant moving row.

Solved by F. H. in 17 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
12/07/2018 - 01:53

Hi..

User
12/07/2018 - 01:53

hi

Excelchat Expert
12/07/2018 - 01:54

ok

Excelchat Expert
12/07/2018 - 01:54

I understand based on a specific criteria you want to move data from master sheet to the next sheet

Excelchat Expert
12/07/2018 - 01:54

can you share the sample of the master sheet

Excelchat Expert
12/07/2018 - 01:55

And the criteria

Excelchat Expert
12/07/2018 - 01:56

ok i see it

Excelchat Expert
12/07/2018 - 01:56

so what is the criteria

User
12/07/2018 - 01:56

Register Asset # Category Asset Description Asset Number BM Number BC Number Size or Distribution Rule Asset Class
Asset 142 Plant & Equipment AS Stick Welder 1001 Welding
Asset 312 Plant & Equipment Gas & Oxy Set 1002 Welding
Asset 126 Plant & Equipment Karcher Model HD10/25SX Cold Water Pressure Was 1003 Washer
Asset 121 Plant & Equipment (New) Sipping container 1004 Container
Asset 149 Plant & Equipment (2nd Hand )Shipping Cont. 1005 criteria is welding , washer, container

User
12/07/2018 - 01:56

oops

User
12/07/2018 - 01:56

criteria is welsding washer container

Excelchat Expert
12/07/2018 - 01:56

ok 1 min

Excelchat Expert
12/07/2018 - 02:01

I have update the sheet

Excelchat Expert
12/07/2018 - 02:01

=ArrayFormula(IFERROR(INDEX(Sheet1!A$2:A$20,SMALL(IF(Sheet1!$H$2:$H$20="Washer",ROW(Sheet1!$B$2:$B$20)-ROW($B$2)+1),ROW(1:1))),"")) this is the formula

User
12/07/2018 - 02:02

where would I write that into?

Excelchat Expert
12/07/2018 - 02:02

as of now i assume Sheet1 has 20 rows.. you can keep incrementing it as you add more data

Excelchat Expert
12/07/2018 - 02:02

this formula is in Washer sheet a2

Excelchat Expert
12/07/2018 - 02:03

all the formula are same except the value is different

Excelchat Expert
12/07/2018 - 02:03

=ArrayFormula(IFERROR(INDEX(Sheet1!A$2:A$20,SMALL(IF(Sheet1!$H$2:$H$20="Container",ROW(Sheet1!$B$2:$B$20)-ROW($B$2)+1),ROW(1:1))),""))

Excelchat Expert
12/07/2018 - 02:03

container

Excelchat Expert
12/07/2018 - 02:03

=ArrayFormula(IFERROR(INDEX(Sheet1!A$2:A$20,SMALL(IF(Sheet1!$H$2:$H$20="Welding",ROW(Sheet1!$B$2:$B$20)-ROW($B$2)+1),ROW(1:1))),""))

Excelchat Expert
12/07/2018 - 02:03

welding

User
12/07/2018 - 02:03

okay great and does that update the whole row or only a2?

Excelchat Expert
12/07/2018 - 02:04

after you paste the formula press cntrl+shift+enter as it is an array formula

Excelchat Expert
12/07/2018 - 02:04

then drag the formula till column H it will keep changing the reference.. please chek b2 -h2 in all the sheet

Excelchat Expert
12/07/2018 - 02:04

b2=ArrayFormula(IFERROR(INDEX(Sheet1!B$2:B$20,SMALL(IF(Sheet1!$H$2:$H$20="Welding",ROW(Sheet1!$B$2:$B$20)-ROW($B$2)+1),ROW(1:1))),""))

Excelchat Expert
12/07/2018 - 02:05

in way you will get the entire row...

Excelchat Expert
12/07/2018 - 02:05

each cell needs to have the formula

Excelchat Expert
12/07/2018 - 02:06

You there

User
12/07/2018 - 02:06

okay brilliant, so if I move the order within the mnastersheet the data will remain the same on the other ones?

Excelchat Expert
12/07/2018 - 02:07

you can change the order in master sheet as long as it is Washer it will appear in the Washer sheet ..the only difference is the position

Excelchat Expert
12/07/2018 - 02:07

so example

Excelchat Expert
12/07/2018 - 02:07

welding

Excelchat Expert
12/07/2018 - 02:07

current order is Asset 142
Asset 312

Excelchat Expert
12/07/2018 - 02:07

in main sheet

Excelchat Expert
12/07/2018 - 02:07

so in welding as well it is Asset 142
Asset 312

User
12/07/2018 - 02:08

oh okay great I understand

User
12/07/2018 - 02:08

thank you very muvh

Excelchat Expert
12/07/2018 - 02:08

your welcome

Excelchat Expert
12/07/2018 - 02:09

download the file for your reference

Excelchat Expert
12/07/2018 - 02:09

have a nice day

Excelchat Expert
12/07/2018 - 02:09

Please do click on end session and leave your feedback :)

User
12/07/2018 - 02:09

thank you you too

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*