Help moving table contents from one sheet to another depending on criteria

[jakedunn]

Hi all,

Can anyone please help with some code that will allow me to move data from one work sheet to a specific location on another sheet, depending on a cell criteria?

In the attached simplified example I have a table in Sheet1 with 4 columns, titled; Name (A1), On (B1), Off (C1) and a drop down menu with the choice of yes or no (D1).

I am wanting to transfer any data in the rows of the first 3 columns of this table to different locations on Sheet2 depending on the value selected in D1 on Sheet1.

For example

If Sheet1 D2 = Yes, than I want to copy Sheet1 A2:C2 to Sheet2 B3:D3
If Sheet1 D3 = No, then I want to copy Sheet1 A3:C3 to Sheet2 F3:H3

I hope that makes sense.. I would be very appreciative if anyone was able to assist with some advice on the best way to perform this please

Thank you again.

[dosydos]

I would Suggest utilizing the FILTER function.
See the attached file/
First thing i would do is on SHeet 2 delete the tables.
I would then would then put your headers at the top as text.
Then in cell B3 i put the formula:

=FILTER(Sheet1!$A$2:$C$19,Sheet1!$D$2:$D$19="Yes")

In cell F3 i used the formula:

=FILTER(Sheet1!$A$2:$C$19,Sheet1!$D$2:$D$19="No")

the formula will spill automatically and return all rows with yes and No accordingly

[jakedunn]

Thank you for your reply but when I attempt to use this or add to the table I am getting #NAME? as the output... Could this be because I am currently using Office Professional Plus 2016 and not 365?

Do you have any other ideas or suggestions to correct this?

Thank you again for your help

[dosydos]

I could have sworn your version said "365" when I posted that. Unfortunately 2016 doesn't support FILTER function. You would need either 2021 or 365.
As for a fix using the below formulas are array formulas so you must use CTRL + SHIFT + ENTER when entering the formula. If done correctly { } will be inserted around the formula automatically:

in cell B3:

=IFERROR(INDEX(Sheet1!A$1:A$19,SMALL(IF(Sheet1!$D$2:$D$19="Yes",ROW(Sheet1!$D$2:$D$19),""),ROW(1:1))),"")

Drag over to column D and then drag down as much as you want.

In cell F3

=IFERROR(INDEX(Sheet1!A$1:A$19,SMALL(IF(Sheet1!$D$2:$D$19="No",ROW(Sheet1!$D$2:$D$19),""),ROW(1:1))),"")

Drag over to column H and then drag down as much as you want.

[jakedunn]

Thank you so much.. that works perfect!!

Is it possible to use the same process on a bit of a more involved/complicated worksheet such as the one I have attached? I'm sure it must be somehow but with my limited excel knowledge I'm finding it difficult..

In the attached on the worksheet "Data" we add staff names, start times, finish times and role (from drop-down list) across columns C:D respectively. I would then like to copy each row to the associated location on the "Daily Staffing" sheet dependant on the selection in the 'assigned row' column.

For example if the entry on row 5 of the "Data" work sheet has "CAT 2" as the assigned role in G5 then I want C5:E5 copied to "Daily Staffing" sheet into the next free row of L14:L27 (Name, Start, Finish) under the Cat 2 heading. This would need to be repeated for each role that we have on the daily staffing sheet.

Is there a relatively easy way to achieve this or is it too complicated? Thank you again for all your help, it is very much appreciated..

[AliGW]

Thread closed. The OP wishes to ask the same question in the Commercial Services section.