Indirect Function Delivering Blank Cell Results

[jonholm619]

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:

	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

 

[Chrisdontm]

I didn't take the time to dig all the way into your new formula, but some observations are;

Why change your formula if it was working...?
&
The INDIRECT formula converts a text to a reference, to show a text,you do so by putting it in quotes...
&
What you have in the INDIRECT formula is a one cell reference, are you meaning to do that;
( i.e.... $AA3 ) instead of: $P$5003:$R$5025 ???

( These are just some quick thoughts, perhaps your answers will stir up some help )

 

[jonholm619]

Hey Chris, thank you for the feedback...The reason for the change is because without using the indirect formula, I would have to manually type into each cell the range because the range changes as I move throughout the various rows.

I tried two different solutions to your advice of adding quotes. The first, INDIRECT('FTP payment poly'!"$AA3"... gave me an error message stating that there was a problem with the formula. The second, INDIRECT("'FTP payment poly'!$AA3"...gave me a blank result.

Any additional help would be greatly appreciated.

Thanks!

 

[Chrisdontm]

First of all as I replicated your situation, you have an extra closing parenthesis in your second SMALL formula to where it doesn't allow the ROW formula to be a part of it.

The INDIRECT formula is picky...

What do you have inside of cell AA3...?

If you have: P5003:R5025 or $P$5003:$R$5025 it should work, as long as you are meaning to refer to that other tab.

Because you are telling it to go to the 'FTP payment poly'! tab and then on that tab go to the text mentioned in cell $AA3, which should be the range I just mentioned...

Then when you copy it down that reference will change to Cell: $AA4 , so you may want to lock that reference to: $AA$3

I am not seeing your spreadsheet, so if I am missing something forgive me...
Just keep in mind that the INDIRECT is strictly looking at everything as text and then converting it.
I hope this helps in some way.

 

[Aladin Akyurek]

In which cell are you entering this first instance of this formula?

 

[Chrisdontm]

I used the INDIRECT in the first INDEX part of the formula, and it worked, but when I put it in the SMALL part of the formula, it didn't...

Then I tried the second INDEX part, and it didn't work there either...

I ran across a thread that says to not put it in MIN, MAX, or AVERAGE...perhaps one shouldn't use it in SMALL either...

The Thread: http://www.mrexcel.com/forum/excel-questions/585305-problem-indirect-function-array-formula.html

 

[jonholm619]

B2 of table 1, which is in a different sheet than table 2.