Auto updatation worksheet

[sachar]

Dear All,

With reference to the attach sample file, I made a separate worksheet of “Status_y” and “Status_t” on the basis of worksheet “master_file” manually. But, I want to be auto updated the status_y and t worksheet when we add a new row in the master worksheet with the help of any formula.

Please help me?

 

[vletm]

sachar - You wrote:
I want to be auto updated the status_y and t worksheet when we add a new row in the master worksheet with the help of any... Not 'formula' but
after all values has entered to new row ...
this will 'auto update' current status_

 

[sachar]

Dear,

Received with thanks, but, my question was that when I insert the new row in the master worksheet by “y” and “t” status, it should be automatically added into the concerned worksheet accordingly.

 

[vletm]

Oh sachar
Who/how will take care that A and B and D-column are filled before 'status'?
If that no need to be filled then ... okay
just confirm that before I'll make minor change.
=> even if ONLY status has filled then new line to Status_y/t - okay?

 

[p45cal]

…with the help of any formula

Try this on sheet status_y:
Select a range bigger than you'll ever need where your data will appear, say A2:D100.
Array-enter this formula (array-entering means pressing Ctrl+Shift+Enter, not just Enter, to commit the formula to the sheet) while all these cells are still selected:
=IFERROR(INDEX(Table1[#All],SMALL(IF(Table1[[#All],[status]]="y",ROW(Table1[[#All],[status]])),ROW()+1-ROW($A$2)),COLUMN()),"")

On sheet status_t, slightly different because your table is lower on the sheet, select say A3:D100 and array-enter this formula in the same way:
=IFERROR(INDEX(Table1[#All],SMALL(IF(Table1[[#All],[status]]="t",ROW(Table1[[#All],[status]])),ROW()+1-ROW($A$3)),COLUMN()),"")

I've highlighted the small differences, red to compensate for where on the sheet the resulting table will be (use the top-left cell of where you're entering the formula), blue for the filter value.
As long as new data entered on the Master_file sheet is within the Table called Table 1 then the cells on both other sheets will update automatically.

You will need to format the columns as you want, especially the date columns

See attached.

 

[sachar]

Dear,

Please anyone can explore the use of SMALL and ROW formula in the following formula?

=IFERROR(INDEX(Table1[#All],SMALL(IF(Table1[[#All],[status]]="y",ROW(Table1[[#All],[status]])),ROW()+1-ROW($A$2)),COLUMN()),"")