jonholm619
New Member
- Joined
- Apr 6, 2016
- Messages
- 11
Hello,
I am coming across issues with results being delivered as a result of substituting the indirect function for the range that had previously existed. Initially I had the following as my formula, which delivered the results I needed:
{=IF(ISERROR(INDEX('FTP payment poly'!$P$5003:$R$5025,SMALL(IF('FTP payment poly'!$P$5003:$R$5025=$A3,ROW('FTP payment poly'!$P$5003:$R$5025)),ROW($1:$1)),3)),"",INDEX('FTP payment poly'!$P$5003:$R$5025,SMALL(IF('FTP payment poly'!$P$5003:$R$5025=$A3,ROW('FTP payment poly'!$P$5003:$R$5025))),ROW($1:$1)),3))}
I then substituted my range with the following (which left me with blank cells (even after ensuring I used CTRL+SHIFT+ENT to activate the array:
{=IF(ISERROR(INDEX(INDIRECT('FTP payment poly'!$AA3),SMALL(IF(INDIRECT('FTP payment poly'!$AA3)=$A3,ROW(INDIRECT('FTP payment poly'!$AA3))),ROW($1:$1)),3)),"",INDEX(INDIRECT('FTP payment poly'!$AA3),SMALL(IF(INDIRECT('FTP payment poly'!$AA3)=$A3,ROW(INDIRECT('FTP payment poly'!$AA3))),ROW($1:$1)),3))}
The above referenced range where 'FTP payment poly'! exists is located on another sheet within the workbook and is the lookup table. This will be the second table displayed below. The first table shown below is where the columns B-F carry the referenced formula above. Columns G-K are helper columns I created formulas in to come up with the appropriate range for the lookup.
What I am attempting to do is run down the list, and produce the next five customer transactions that occurred after a specified return. The array works, but as stated earlier only prior to inserting the indirect formula. Once I use the indirect formula, my results do not exist. I do not get any error messages, simply blank cells. The results delivered prior to inserting the indirect function, were 1st Status for customer 000253073 was 4/8/15 and 2nd Status for customer 000253073 was 4/9/15. Technically, the formula above would have been slightly changed where ROW($1:$1) exists for 1st Status to include ROW($2:$2) would exist for 2nd Status.
Please note I have simplified this example and in reality there are several thousands of rows that these customers belong to and the reason for the need for the array function.
Table 1:
<tbody>
</tbody>
Table 2:
<tbody>
</tbody>
Thank you in advance!
~Jon
I am coming across issues with results being delivered as a result of substituting the indirect function for the range that had previously existed. Initially I had the following as my formula, which delivered the results I needed:
{=IF(ISERROR(INDEX('FTP payment poly'!$P$5003:$R$5025,SMALL(IF('FTP payment poly'!$P$5003:$R$5025=$A3,ROW('FTP payment poly'!$P$5003:$R$5025)),ROW($1:$1)),3)),"",INDEX('FTP payment poly'!$P$5003:$R$5025,SMALL(IF('FTP payment poly'!$P$5003:$R$5025=$A3,ROW('FTP payment poly'!$P$5003:$R$5025))),ROW($1:$1)),3))}
I then substituted my range with the following (which left me with blank cells (even after ensuring I used CTRL+SHIFT+ENT to activate the array:
{=IF(ISERROR(INDEX(INDIRECT('FTP payment poly'!$AA3),SMALL(IF(INDIRECT('FTP payment poly'!$AA3)=$A3,ROW(INDIRECT('FTP payment poly'!$AA3))),ROW($1:$1)),3)),"",INDEX(INDIRECT('FTP payment poly'!$AA3),SMALL(IF(INDIRECT('FTP payment poly'!$AA3)=$A3,ROW(INDIRECT('FTP payment poly'!$AA3))),ROW($1:$1)),3))}
The above referenced range where 'FTP payment poly'! exists is located on another sheet within the workbook and is the lookup table. This will be the second table displayed below. The first table shown below is where the columns B-F carry the referenced formula above. Columns G-K are helper columns I created formulas in to come up with the appropriate range for the lookup.
What I am attempting to do is run down the list, and produce the next five customer transactions that occurred after a specified return. The array works, but as stated earlier only prior to inserting the indirect formula. Once I use the indirect formula, my results do not exist. I do not get any error messages, simply blank cells. The results delivered prior to inserting the indirect function, were 1st Status for customer 000253073 was 4/8/15 and 2nd Status for customer 000253073 was 4/9/15. Technically, the formula above would have been slightly changed where ROW($1:$1) exists for 1st Status to include ROW($2:$2) would exist for 2nd Status.
Please note I have simplified this example and in reality there are several thousands of rows that these customers belong to and the reason for the need for the array function.
Table 1:
A | B | C | D | E | F | G | H | I | J | K | |
1 | Customer | 1st Status | 2nd Status | 3rd Status | 4th Status | 5th Status | Row where first customer is found | Transaction ID where a customer and their return is found | Row where the transaction ID appears | Row to begin | Endingrow |
2 | 000253073 | 5000 | 83793 | 5002 | 5003 | 5025 | |||||
3 | 000124580 | 5005 | 82532 | 5007 | 5008 | 5025 | |||||
4 | 000252063 | 5010 | 83986 | 5010 | 5011 | 5025 | |||||
5 | 000257283 | 5012 | 83850 | 5012 | 5013 | 5025 | |||||
6 | 000252725 | 5014 | 84683 | 5017 | 5018 | 5025 | |||||
7 | 000254700 | 5019 | 83587 | 5019 | 5020 | 5025 | |||||
8 | 000173540 | 5020 | 82530 | 5020 | 5021 | 5025 | |||||
9 | 000112938 | 5021 | 82534 | 5023 | 5024 | 5025 | |||||
10 | 000244966 | 5024 | 83870 | 5024 | 5025 | 5025 |
<tbody>
</tbody>
Table 2:
P | Q | R | |
5000 | 253073 | Processed | 4/5/15 |
5001 | 253073 | Processed | 4/6/15 |
5002 | 253073 | Processed | 4/7/15 |
5003 | 253073 | Processed | 4/8/15 |
5004 | 253073 | Processed | 4/9/15 |
5005 | 124580 | Processed | 4/3/15 |
<tbody>
</tbody>
Thank you in advance!
~Jon