Multiple IF statements referencing another sheet

SummerEdwards

Hello,

I'm trying to create a formula that references data in another sheet and looks at multiple criteria from two columns: 1. the name of the person and 2. the entry falls between two dates, then I want to return the value from that sheet.  Wondering if I need and IF(AND(  formula?  Here's what I have so far:

=IF({Auditor}, "Staff Name", IF({Date} >= DATE(2019, 4, 1), IF({Date} <= DATE(2019, 4, 6), {Audit#}))) 

Thanks!

Paul Newcome

Are you trying to pull specific data from the master sheet in reference to the Staff Name and Date ranges or are you trying to count how many occurrences there are?

SummerEdwards

Trying to pull specific data from a master sheet by referencing the name and date range.  So if the entry was made by Person A and the same entry was made between these dates, put the cell value from column X (displayed on the same master sheet as the name and date range) on the sheet that has the formula.  I hope that helps. 

Paul Newcome

It does help. 

 

What are the chances that Person A will have multiple entries within the specified date range?

SummerEdwards

It is possible and likely. 

Paul Newcome

Then you are going to want to use a JOIN/COLLECT statement. Something along the lines of...

 

=JOIN(COLLECT({Display Range}, {Person's Name Range}, "Person A", {Date Range}, AND(@cell <= DATE(2019, 4, 6), @cell >= DATE(2019, 4, 1))), " - ")

.

{Display Range}: Cross sheet reference of the column holding the data you are wanting to pull/display.

{Person's Name Range}: Same as above except referencing the column in the master sheet that contains the list of names.

"Person A": The person's name. A cell reference can also be used to avoid having to edit the formula for each individual.

{Date Range}: Cross Sheet reference of the column housing the dates.

" - ": Delimiter to separate multiple entries being displayed.

.

So if Person A entered "ABC" and "DEF" and "123" within the date range, it would be displayed as:

ABC - DEF - 123