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.