Expert Solution - Excel IF Problems

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

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?