Create a list on multiple criteria from a dynamic master sheet

Dee Dee · Sep 18, 2020

Hi,

I have a question that I am not sure if I am explaining right. I have a full database of information and I want to make a new sheet with a list off of that database carrying over three columns of information IF one other column in the database indicates Yes. I somehow cannot think of how to ask the question to be able to look for the answer. Any assistance would be appreciated. Clarification is probably needed I know.

Dee Dee · Sep 20, 2020

Fluff said:
Ok, that means you don't have the latest update yet. Try

+Fluff New.xlsm
A B C D
1 AGGRIEVED PERSON COMMISSARY/HQ EMPLOYMENT STATUS INITIAL CONTACT DATE
2 James Brown bottle unemployed 01/08/2020
3 Lucille Ball care secretary 02/01/2020
4 Michael Jordan shoe tailor 04/03/2020
5 Louise Jefferson can thief 07/11/2019
6 JJ Walker mat carpenter 02/03/2018
7 Jane Doe war laithworker 30/09/2019
8
9
10
11
12
13
14
Sheet2
Cell Formulas
Range Formula
A2:D10 A2 =IFERROR(INDEX(Sheet1!B$2:B$100,AGGREGATE(15,6,(ROW(Sheet1!$M$2:$M$100)-ROW(Sheet1!$M$2)+1)/(Sheet1!$M$2:$M$100="y"),ROWS(A$2:A2))),"")

Ok I think Im doing something really wrong. The formula gives me no results. I was trying to work on a similar formula that I couldn't get done.

You refer to Sheet 1, is that my source or destination? Because I am figuring that is the source of the information so I entered the formula with the source sheet name wherever I saw sheet 1. I think the mistake I made was to think I would be sharing the entire document since this is the first time Im using a forum. The tabs have different names for me.

Fluff · Sep 20, 2020

Yes, you will need to change Sheet1 to whatever your source sheet is called. As the source sheet has spaces in the name you will need to wrap it in single quotes like

Excel Formula:

INDEX('Contacts (No counseling)'!B$2:B$100

Dee Dee · Sep 20, 2020

alansidman said:
Cannot determine what is wrong in your situation. It worked perfectly with the data you provided. Do you have a second tab named "Sheet2"

Complaints Tracker - FY21.xlsm

A

B

C

D

E

F

1

Days Open

Case #

Complainant's Name

Employment Status

Date of Formal Filing

Date Acknowledment Letter Sent

2

36

-65

8/15/2020

8/20/20

3

174

3/30/2020

4/2/20

4

374

9/12/2019

9/30/19

5

-8

9/28/2020

9/30/21

6

385

9/1/2019

9/28/21

7

8

9

10

Formal Complaint Tracking

Cell Formulas
Range	Formula
B2	B2	=IFERROR(INDEX(Counselings!B$2:B$100,AGGREGATE(15,6,(ROW(Counselings!$M$2:$M$100)-ROW(Counselings!$M$2)+1)/(Counselings!$M$2:$M$100="y"),ROWS(A$2:A2))),"")
A2:A10	A2	=IF(TODAY()-E2>4000,"",TODAY()-E2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:F7	Cell Value	between 44105 and 44469	text	NO
D2:F7	Cell Value	between 43374 and 43738	text	NO
D2:F7	Cell Value	between 43739 and 44104	text	NO
D2:F7	Cell Value	above average	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D1:D10	List	=DropDown!$A$2:$A$5

Dee Dee · Sep 20, 2020

That is how it looks when I paste the formula in b2

Dee Dee said:
Complaints Tracker - FY21.xlsm
A B C D E F
1 Days Open Case # Complainant's Name Employment Status Date of Formal Filing Date Acknowledment Letter Sent
2 36 -65 8/15/2020 8/20/20
3 174 3/30/2020 4/2/20
4 374 9/12/2019 9/30/19
5 -8 9/28/2020 9/30/21
6 385 9/1/2019 9/28/21
7
8
9
10
Formal Complaint Tracking
Cell Formulas
Range Formula
B2 B2 =IFERROR(INDEX(Counselings!B$2:B$100,AGGREGATE(15,6,(ROW(Counselings!$M$2:$M$100)-ROW(Counselings!$M$2)+1)/(Counselings!$M$2:$M$100="y"),ROWS(A$2:A2))),"")
A2:A10 A2 =IF(TODAY()-E2>4000,"",TODAY()-E2)
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
D2:F7 Cell Value between 44105 and 44469 text NO
D2:F7 Cell Value between 43374 and 43738 text NO
D2:F7 Cell Value between 43739 and 44104 text NO
D2:F7 Cell Value above average text NO
Cells with Data Validation
Cell Allow Criteria
D1:D10 List =DropDown!$A$2:$A$5

Im confusing myself because Im tired. That is not the right sheet I sent this second time. Contacts should send to sheet called counseling. Contacts is the first one I sent before. This one is the Counseling sheet. The destination for the formula is D2 in Counseling.

I appreciate you sticking with me through this. Im working and cooking and cleaning at the same time.

Complaints Tracker - FY21.xlsm

A

B

C

D

E

F

G

1

Days Open

Counseling Days Remaining

ADR Days Remaining

iComplaints/Case #

Aggrieved Person (Name)

Employment Status

Date of Initial Contact

2

3

4

5

6

7

8

9

10

11

12

Counselings

Cell Formulas
Range	Formula
A2:A12	A2	=IF(G2="", "", IF($M2="Y", (N2-$G2), IF(O2="Y", (P2-G2), IF(X2="Y", (Y2-G2), TODAY()-G2))))
B2:B12	B2	=IF(A2="","",SUM(SUM(IF(K2="y",G2+90,G2+30)-G2)-A2))
C2:C12	C2	=IF(A2="","",SUM(SUM(IF(V2="y",G2+90," ")-G2)-A2))
D2	D2	=IFERROR(INDEX(Contacts!B$2:B$100,AGGREGATE(15,6,(ROW(Contacts!$M$2:$M$100)-ROW(Contacts!$M$2)+1)/(Contacts!$M$2:$M$100="y"),ROWS(A$2:A2))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B371	Expression	=$B2<10	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F1:F12	List	=DropDown!$A$2:$A$5

Fluff · Sep 20, 2020

Is the source sheet sheet called Contacts, or Contacts (No counseling)

Dee Dee · Sep 20, 2020

The source is Contacts. I changed the name of the tab because it had some brackets in it that the computer didn't seem to want to take.

Fluff · Sep 20, 2020

That should work

+Fluff New.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

1

ICOMPLAINTS #

AGGRIEVED PERSON

COMMISSARY/HQ

EMPLOYMENT STATUS

INITIAL CONTACT DATE

ASSIGNED COUNSELOR

BASIS(ES)

ISSUE(S)

HARASSMENT ALLEGED (Y/N)?

HARSSMENT QUESTIONS ISSUED (Y/N)?

DATE OF MOST RECENT ALLEGED ACT

DATE OF CLOSURE

Previous Counseling (Y/N)

2

abc1

James Brown

bottle

unemployed

01/08/2020

Y

3

abc2

Lucille Ball

care

secretary

02/01/2020

y

4

abc3

Michael Jordan

shoe

tailor

04/03/2020

y

5

abc4

Mickey Mouse

slipper

dealer

02/09/2020

n

6

abc5

George Jefferson

dope

doctor

01/12/2019

n

7

abc6

Louise Jefferson

can

thief

07/11/2019

y

8

abc7

JJ Walker

mat

carpenter

02/03/2018

y

9

abc8

Jane Doe

war

laithworker

30/09/2019

y

10

abc9

Captain Ahab

dog

stevedore

30/09/2018

n

Contacts

+Fluff New.xlsm

A

B

C

D

E

F

G

1

Days Open

Counseling Days Remaining

ADR Days Remaining

iComplaints/Case #

Aggrieved Person (Name)

Employment Status

Date of Initial Contact

2

3

4

5

6

7

8

9

10

11

12

Sheet2

Cell Formulas
Range	Formula
A2:A12	A2	=IF(G2="", "", IF($M2="Y", (N2-$G2), IF(O2="Y", (P2-G2), IF(X2="Y", (Y2-G2), TODAY()-G2))))
B2:B12	B2	=IF(A2="","",SUM(SUM(IF(K2="y",G2+90,G2+30)-G2)-A2))
C2:C12	C2	=IF(A2="","",SUM(SUM(IF(V2="y",G2+90," ")-G2)-A2))
D2:E12	D2	=IFERROR(INDEX(Contacts!A$2:A$100,AGGREGATE(15,6,(ROW(Contacts!$M$2:$M$100)-ROW(Contacts!$M$2)+1)/(Contacts!$M$2:$M$100="y"),ROWS(A$2:A2))),"")
F2:G12	F2	=IFERROR(INDEX(Contacts!D$2:D$100,AGGREGATE(15,6,(ROW(Contacts!$M$2:$M$100)-ROW(Contacts!$M$2)+1)/(Contacts!$M$2:$M$100="y"),ROWS(C$2:C2))),"")

Dee Dee · Sep 21, 2020

Fluff said:
That should work

+Fluff New.xlsm
A B C D E F G H I J K L M
1 ICOMPLAINTS # AGGRIEVED PERSON COMMISSARY/HQ EMPLOYMENT STATUS INITIAL CONTACT DATE ASSIGNED COUNSELOR BASIS(ES) ISSUE(S) HARASSMENT ALLEGED (Y/N)? HARSSMENT QUESTIONS ISSUED (Y/N)? DATE OF MOST RECENT ALLEGED ACT DATE OF CLOSURE Previous Counseling (Y/N)
2 abc1 James Brown bottle unemployed 01/08/2020 Y
3 abc2 Lucille Ball care secretary 02/01/2020 y
4 abc3 Michael Jordan shoe tailor 04/03/2020 y
5 abc4 Mickey Mouse slipper dealer 02/09/2020 n
6 abc5 George Jefferson dope doctor 01/12/2019 n
7 abc6 Louise Jefferson can thief 07/11/2019 y
8 abc7 JJ Walker mat carpenter 02/03/2018 y
9 abc8 Jane Doe war laithworker 30/09/2019 y
10 abc9 Captain Ahab dog stevedore 30/09/2018 n
Contacts

+Fluff New.xlsm
A B C D E F G
1 Days Open Counseling Days Remaining ADR Days Remaining iComplaints/Case # Aggrieved Person (Name) Employment Status Date of Initial Contact
2 50 -20 #VALUE! abc1 James Brown unemployed 01/08/2020
3 262 -232 #VALUE! abc2 Lucille Ball secretary 02/01/2020
4 200 -170 #VALUE! abc3 Michael Jordan tailor 04/03/2020
5 318 -288 #VALUE! abc6 Louise Jefferson thief 07/11/2019
6 933 -903 #VALUE! abc7 JJ Walker carpenter 02/03/2018
7 356 -326 #VALUE! abc8 Jane Doe laithworker 30/09/2019
8
9
10
11
12
Sheet2
Cell Formulas
Range Formula
A2:A12 A2 =IF(G2="", "", IF($M2="Y", (N2-$G2), IF(O2="Y", (P2-G2), IF(X2="Y", (Y2-G2), TODAY()-G2))))
B2:B12 B2 =IF(A2="","",SUM(SUM(IF(K2="y",G2+90,G2+30)-G2)-A2))
C2:C12 C2 =IF(A2="","",SUM(SUM(IF(V2="y",G2+90," ")-G2)-A2))
D2:E12 D2 =IFERROR(INDEX(Contacts!A$2:A$100,AGGREGATE(15,6,(ROW(Contacts!$M$2:$M$100)-ROW(Contacts!$M$2)+1)/(Contacts!$M$2:$M$100="y"),ROWS(A$2:A2))),"")
F2:G12 F2 =IFERROR(INDEX(Contacts!D$2:D$100,AGGREGATE(15,6,(ROW(Contacts!$M$2:$M$100)-ROW(Contacts!$M$2)+1)/(Contacts!$M$2:$M$100="y"),ROWS(C$2:C2))),"")

Thank you so very much! I really do appreciate all the help that you gave me on this!!!

Fluff · Sep 21, 2020

You're welcome & thanks for the feedback.

Dee Dee · Sep 21, 2020

Hey one more question. Now that i have the information on the required sheet, I notice that if there is a filter applied, this info does not filter with the rest of the sheet. Is there a way to keep the information that is in the same row together when filtered? Or will I have to advise DO NOT FILTER?

Create a list on multiple criteria from a dynamic master sheet

Dee Dee

New Member

Dee Dee

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

Dee Dee

New Member

Dee Dee

New Member

Fluff

MrExcel MVP, Moderator

Dee Dee

New Member

Fluff

MrExcel MVP, Moderator

Dee Dee

New Member

Fluff

MrExcel MVP, Moderator

Dee Dee

New Member

Similar threads

Forum statistics

Share this page

Cell Formulas
Range	Formula
A2:D10	A2	=IFERROR(INDEX(Sheet1!B$2:B$100,AGGREGATE(15,6,(ROW(Sheet1!$M$2:$M$100)-ROW(Sheet1!$M$2)+1)/(Sheet1!$M$2:$M$100="y"),ROWS(A$2:A2))),"")

Create a list on multiple criteria from a dynamic master sheet

New Member

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock