Multiple IF statements referencing another sheet

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!

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…