CODEWITHSUNDEEP
vbaexcel
Md Riad Hossain
Md Riad Hossain

Reputation: 291

Insert row with data into excel sheet from another sheet dynamically

I have two different excel sheet first sheet contain some country list and second sheet is blank. There is a dropdown column with every country which contain 'Yes' 'No'. When I select yes I want that, the selected country row will automatically add to second sheet with all data. When I select more country they will dynamically insert/add to second sheet with maintaining country order in the first sheet. Excel dynamic row insert These can be done with macro but I want to do this with formula.

Upvotes: 1

Views: 2955

Answers (1)

L42
L42

Reputation: 19727

Try this:

=IFERROR(INDEX(Sheet1!$A$1:$C$7,SMALL(IF(Sheet1!$D$1:$D$7="Yes",ROW(Sheet1!$D$1:$D$7),1E+99),ROW($A1)),COLUMN(A$1)),"")

Input formula in Cell A2 of Sheet2 and press Ctrl+Shift+Enter.
Then copy to remaining cells. Adjust the addresses in the formula to suit. HTH

Result:

enter image description here

Upvotes: 3

Related Questions