samyscraps01
Board Regular
- Joined
- Jul 6, 2017
- Messages
- 56
Good Afternoon,
I would like some help on a formula for IF(OR. I have attached the spreadsheet for reference. I would like the formula to do two things:
1. when the category drop-down menu is on ALL. I would like it to display all the results on cell C7 and C8.
2. If a date is not available because it is is blank rather than having it return a random date as it has been doing. I would like it to just stay blank.
I have been trying to modify the formula but it is not working well even though I added the all into it. Also, does the formula need to become an array for it to list all possible dates once I select all?
For reference, I am not going to be posting this on other sites.
Thanks.
http://IFOR.JPG
<tbody>
</tbody>What i would like is two things.
1. when I select a given employee and I select ALL from the Category drop-down menu then give me all the dates at once associated with that employee. They should populate in Cells C7 and C8. This is coming from the informatoin on tab Y of the same spreadsheet.
2. If there is nothing in the cell, then return a blank rather than a random date.
Main Spreadsheet Formula
=IF(OR($A$2="",$B$2="",SUMPRODUCT((Sheet1!A$2:A$10=$A$2)*(Sheet1!B$1:D$1=$B$2)+($A$5="ALL")*(Sheet1!B$2:D$10))=0),"",SUMPRODUCT((Sheet1!A$2:A$10=$A$2)*(Sheet1!B$1:D$1=$B$2)*(Sheet1!B$2:D$10))) FORMULA IN CELL C2
=IF(OR($B$2="ALL", $B$2="ALL"),"Delivery","") FORMULA IN CELL C7, THESE FORMULAS ONLY APPEAR WHEN ALL IN CELL B2 IS SELECTED
=IF(OR($B$2="ALL", $B$2="ALL"),"Purchase","") FORMULA IN CELL C8, " "
TAB Y
<tbody>
</tbody>
IF CELL IS BLANK LIKE IN C3 DO NOT ENTER A DATE, IT'S BEEN INPUTTING A DATE OF 1/0/1900. NOT SURE WHY BUT I WANT IT TO STAY BLANK.
I would like some help on a formula for IF(OR. I have attached the spreadsheet for reference. I would like the formula to do two things:
1. when the category drop-down menu is on ALL. I would like it to display all the results on cell C7 and C8.
2. If a date is not available because it is is blank rather than having it return a random date as it has been doing. I would like it to just stay blank.
I have been trying to modify the formula but it is not working well even though I added the all into it. Also, does the formula need to become an array for it to list all possible dates once I select all?
For reference, I am not going to be posting this on other sites.
Thanks.
http://IFOR.JPG
A | B | C | D | |
1 Employee | Category | Date | ||
2 Thorndale | ALL | |||
3 | ||||
7 | Delivery | DISPLAY 1/17/18 HERE | ||
8 | Purchase | NOTHING IN CELL C3, LEAVE BLANK |
<tbody>
</tbody>
1. when I select a given employee and I select ALL from the Category drop-down menu then give me all the dates at once associated with that employee. They should populate in Cells C7 and C8. This is coming from the informatoin on tab Y of the same spreadsheet.
2. If there is nothing in the cell, then return a blank rather than a random date.
Main Spreadsheet Formula
=IF(OR($A$2="",$B$2="",SUMPRODUCT((Sheet1!A$2:A$10=$A$2)*(Sheet1!B$1:D$1=$B$2)+($A$5="ALL")*(Sheet1!B$2:D$10))=0),"",SUMPRODUCT((Sheet1!A$2:A$10=$A$2)*(Sheet1!B$1:D$1=$B$2)*(Sheet1!B$2:D$10))) FORMULA IN CELL C2
=IF(OR($B$2="ALL", $B$2="ALL"),"Delivery","") FORMULA IN CELL C7, THESE FORMULAS ONLY APPEAR WHEN ALL IN CELL B2 IS SELECTED
=IF(OR($B$2="ALL", $B$2="ALL"),"Purchase","") FORMULA IN CELL C8, " "
TAB Y
A | B | C | D | |
1 Employee | Delivery | Purchase | ALL | |
2 Smith | 2/21/18 | 1/10/18 | ||
3 Thorndale | 1/17/18 |
<tbody>
</tbody>
IF CELL IS BLANK LIKE IN C3 DO NOT ENTER A DATE, IT'S BEEN INPUTTING A DATE OF 1/0/1900. NOT SURE WHY BUT I WANT IT TO STAY BLANK.
Last edited by a moderator: