Solved Find specific data within another worksheet

September 16, 2013 at 14:49:08
Specs: Windows 7
Hi there,
This is a little bit more in depth than my last question. It is an amalgamation of two previous questions. The following is a formula I have created

=IF(AS3=0,0,INDEX(INDIRECT("'"&AS3&"'!A5:ZZ26),(MATCH(AS7,(INDIRECT("'"&AS3&"'!A5:ZZ5),0)+11),(MATCH(AQ5,(INDIRECT("'"&AS3&"'!A8:ZZ26),0)+12)))))

AS3 = the name of a worksheet
A5:ZZ26 is the array within that worksheet
AS7 = Date A5:ZZ26 is the row in which the date can be found
AQ5 = value to look for

example of the answer Date is found in N5
Value found in L8
Returned value = P8

Where have I gone wrong with this formula??????


See More: Find specific data within another worksheet

Report •


#1
September 16, 2013 at 15:00:02
Without testing anything, I'll take a shot..

The INDIRECT function is looking for a text representative of a range reference. Therefore, all parts of the reference need to be inside the quotes so that INDIRECT sees it as test.

It doesn't look like you have closed the quotes after the second & in any of your INDIRECT functions.

Maybe something like this...

INDIRECT("'"&AS3&"'!A5:ZZ5")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 17, 2013 at 10:38:33
=INDEX(INDIRECT("'"&AS3&"'!M8:ZZ26"),(MATCH(AS7,(INDIRECT("'"&AS3&"'!N5:ZZ5")),0)+2),(MATCH(AQ10,(INDIRECT("'"&AS3&"'!L8:L26")),0)))

Match AS7 comes up with 241+2 = 243 Column IV
Match AQ10 comes up with 1 Row 8

However the answer comes as #REF!

I cannot see why this is occurring, any ideas??


Report •

#3
September 17, 2013 at 11:27:19
✔ Best Answer
Obviously without your data, it's hard to test your formula.

That said, just from reading your post, maybe I'm missing something but consider this...

The syntax of the INDEX function is:

=INDEX(array, row_num, [column_num])

Breaking down your formula, I get:

array:       INDIRECT("'"&AS3&"'!M8:ZZ26"),
row_num:     MATCH(AS7,(INDIRECT("'"&AS3&"'!N5:ZZ5")),0)+2,
column_num:  MATCH(AQ10,(INDIRECT("'"&AS3&"'!L8:L26")),0))

In your post you said:

"Match AS7 comes up with 241+2 = 243 Column IV"

and

"Match AQ10 comes up with 1 Row 8"

While I'm not quite sure what you mean by "1 Row 8", it looks to me like perhaps your matches are reversed. You don't have 243 Rows in your array, so Excel is telling you that you are referencing a cell that is outside of your array.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 17, 2013 at 14:12:03
=IF(AS3="",0,IF(AS7>DASHBOARD!$R$1,0,INDEX(INDIRECT("'"&AS3&"'!$M$8:ZZ26"),(MATCH(AQ10,(INDIRECT("'"&AS3&"'!$L$8:L26")),0)),(MATCH(AS7,(INDIRECT("'"&AS3&"'!$N$5:ZZ5")),0)+3))))

Finally cracked the code with the help of DerbyDad03, I seem to be doing silly things and not getting the right answer. Thank the stars you are at the other end. I think I know understand the INDEX,Match criteria.
Once again many thanks for your assistance


Report •


Ask Question