MattDaddyRamos
New Member
- Joined
- Sep 11, 2013
- Messages
- 45
Hi All,
I have two sheets that I am working with:
Sheet 1:
I need each cell in column I to return the number of times any date is populated in columns D-I when the loan numbers match
I need each cell in column J to return the last (highest/closest) date from the dates in columns D-I when the loan numbers match
BOTH need to ignore blanks and if there are NO dates at all return a blank cell
<TBODY>
</TBODY>
Sheet 2:
<TBODY>
</TBODY>
**For Count I've tried: =COUNTIF(INDEX('Sheet 2'!D:I,MATCH('Sheet 1'!A8,'Sheet 2'!C:C,0),0),">"&'Sheet 1'!$A$1) - where A1 is =NOW()
But when I copy the formula down to the other rows it still only counts the dates in the top row.
**For Last Date I've tried:=MAX(IF(NOT(ISERROR('Sheet 2'!D:I)),IF('Sheet 2'!C:C,'Sheet 1'!A8),'Sheet 2'!D:I))
But when I copy the formula down to the other rows it still only reads the dates in the top row.
I hope that wasn't super confusing. Any help you can offer would be greatly appreciated.
Thanks,
MattDaddyRamos
I have two sheets that I am working with:
- Sheet 1:
- Has the cell I want to return the result in
- Has a Specific Identifier I need to be matched
- Sheet 2:
- Has a Column of multiple identifiers that is used to match the specific identifier on Sheet 1
- Has a ROW of 6 unique dates for each matching identifier
Sheet 1:
I need each cell in column I to return the number of times any date is populated in columns D-I when the loan numbers match
I need each cell in column J to return the last (highest/closest) date from the dates in columns D-I when the loan numbers match
BOTH need to ignore blanks and if there are NO dates at all return a blank cell
A | B | C | D | E | F | G | H | I | J | |
1 | Loan # | # of Dates | Last Date | |||||||
2 | 1234 | 6 | 03/05/14 | |||||||
3 | 4567 | 4 | 03/01/14 | |||||||
4 | 7890 | 6 | 03/01/14 | |||||||
5 | 4321 | |||||||||
6 | 7654 | |||||||||
7 | 1098 | |||||||||
8 | 9632 | |||||||||
9 | 7412 |
<TBODY>
</TBODY>
Sheet 2:
A | B | C | D | E | F | G | H | I | |
1 | Loan # | Date 1 | Date 2 | Date 3 | Date 4 | Date 5 | Date 6 | ||
2 | 7412 | 01/05/14 | 01/20/14 | 02/10/14 | |||||
3 | 1098 | 01/03/14 | |||||||
4 | 7890 | 01/10/14 | 01/15/14 | 02/05/14 | 02/10/14 | 02/20/14 | 03/01/14 | ||
5 | 7654 | ||||||||
6 | 4321 | 02/10/14 | 03/01/14 | ||||||
7 | 1234 | 01/01/14 | 01/29/14 | 02/10/14 | 02/20/14 | 02/25/14 | 03/05/14 | ||
8 | 9632 | ||||||||
9 | 4567 | 01/10/14 | 02/10/14 | 02/25/14 | 03/01/14 |
<TBODY>
</TBODY>
**For Count I've tried: =COUNTIF(INDEX('Sheet 2'!D:I,MATCH('Sheet 1'!A8,'Sheet 2'!C:C,0),0),">"&'Sheet 1'!$A$1) - where A1 is =NOW()
But when I copy the formula down to the other rows it still only counts the dates in the top row.
**For Last Date I've tried:=MAX(IF(NOT(ISERROR('Sheet 2'!D:I)),IF('Sheet 2'!C:C,'Sheet 1'!A8),'Sheet 2'!D:I))
But when I copy the formula down to the other rows it still only reads the dates in the top row.
I hope that wasn't super confusing. Any help you can offer would be greatly appreciated.
Thanks,
MattDaddyRamos