Non-VBA - Copy Rows to New Sheet if Cell Contains Specific Text - Self Adapting

WillowLion

New Member
Joined
May 3, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,
I am attempting to copy from one sheet to another based on the value of a cell with a data validation list. I need the formula to adapt to information from Sheet1 if data should change.
Details below:
I need to copy rows of data (columns A-F only) from Sheet 1 to Sheet2 IF data in column F of Sheet 1 contains "Enrolled" (does not need to be an exact match). Column F contains a dependent data validation list. Needs to copy by row, columns a-f only into sheet2 of the same workbook (destination are the same columns, A-F).

Sheet1:
1651605126404.png



Dependent Data Validation List - Sheet1:

1651605137609.png



Sheet2 (Columns G-H shown only as example, titles redacted for sensitive data):

1651605149030.png





*Note: I CANNOT use VBA or macros as this workbook needs to operate on Excel online. I also don't have Scripts.
Can this be done?
Thank you for any help you all can give!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

JamesCanale

Well-known Member
Joined
Jan 13, 2021
Messages
689
Office Version
  1. 365
Platform
  1. Windows
The FILTER function aught to make quick work of it:

MrExcelPlayground8 (version 1).xlsb
ABCDEFGHIJKLMN
1MRNDateNameNameLastCall TypeDispoMRNDateNameNameLastCall TypeDispo
215/3/2022AJonesInboundFollow up25/1/2022BSmithOutboundEnrolled - Dual
325/1/2022BSmithOutboundEnrolled - Dual45/5/2022dNamedInboundPatient Unavailable but Enrolled
435/5/2022CTestInboundPatient Follow Up
545/5/2022dNamedInboundPatient Unavailable but Enrolled
655/2/2022eAnyoneOutboundFollow Up
765/7/2022fTroubleInboundPatient Followup
Sheet18
Cell Formulas
RangeFormula
I2:N3I2=FILTER(A2:F7,ISNUMBER(SEARCH("Enrolled",F2:F7)))
Dynamic array formulas.
 
Solution

Forum statistics

Threads
1,176,096
Messages
5,901,373
Members
434,887
Latest member
zoath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top