Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc