Index function works in A2, but not in A10, Why is that? Cant figure it out.

[sonar]

Hi

I have still tried to make heads or tail out of this, and still cant
get it right.

I have attatched a file, what is it that I need to modify here? I have
tried changing the ranges, but it seems as if it is 9 rows out. any
idea anyone?

Regards
Sonar


+-------------------------------------------------------------------+
|Filename: test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3755 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

You are trying to reference outside of the range.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Sandy Mann]

Most people will not open an attachment for fear of viruses and other
nasties. Try to describe your problem in text and I am sure that someone
will help you

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Biff]

Hi!

I'm looking at your file. What are you trying to do?

It looks like you want to extract data from column A and B if column V is
not blank. Is that correct?

Biff

 

[sonar]

Hi Biff,

You are quite correct. The problem now is, is that my information in
3DBC only starts on line 10 in the original file, and has to be
displayed in line 10 in sheet "Short". But I dont know how to change
the range to display it correctly. Can you help with this?

Thanks.
Sonar

 

[Biff]

Hi!

Based on your sample file...

In Short!A10 enter this formula using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNT('3DBC'!$V$10:$V$25),INDEX('3DBC'!A$10:A$25,SMALL(IF('3DBC'!$V$10:$V$25<>"",ROW($1:$16)),ROW(1:1))),"")

Copy across to B10 then down until you get blanks.

Note: ROW($1:$16) refers to the SIZE of the range. The physical location of
the range is row 10:25. This range contains 16 elements, thus: ROW($1:$16).

If you don't feel like counting how many elements are in the range you can
use a dynamic method right in the formula:

=IF(ROWS($1:1)<=COUNT('3DBC'!$V$10:$V$25),INDEX('3DBC'!A$10:A$25,SMALL(IF('3DBC'!$V$10:$V$25<>"",ROW(INDIRECT("1:"&COUNT('3DBC'!$A$10:$A$25)))),ROW(1:1))),"")

This also assumes that there will be no empty cells within the range of
column A. (none in your sample)

I like how you shortened those sheet names! <g>

Biff

 

[sonar]

Hi Biff

Thanks for the help, my only problem now is, is that I get a #Num erro
from row 11 onwards when I want to try and change the range from 10:999
why is that?

(=IF(ROWS($1:2)<=COUNT('3DBC'!$V$10:$V$999),INDEX('3DBC'!A$10:A$999
SMALL(IF('3DBC'!$V$10:$V$999<>"",ROW($1:$999)),ROW(2:2))),"")}

If I can understand what it is that rules this, I can manage it.

Regards
Sona

 

[sonar]

Hi Biff

I decided to look at modifying the second option, and that worked
perfectly.

Thanks for all the help.

You guys rock!

Regards
Sonar

 

[Biff]

Hi!

If I can understand what it is that rules this, I can manage it.

Actually, this is a very simple formula once you understand how it works.

The problem you encountered is the one most people have with this type of
formula.

The INDEX function is used to hold an array of values. In this case that
array is A10:A999. This array holds a total of 990 values. Even though the
physical location of this array is A10:A999, the "virtual array" that is
being held in the INDEX function starts with position 1 through position
990. Where:

A10 is in the 1st position
A11 is in the 2nd position
A12 is in the 3rd position
....
A999 is in the 990th position

Now, this is where the ROW($1:$999) function comes into play and is where
people usually make their mistakes. The argument to the ROW( ) function
*MUST MATCH EXACTLY* the SIZE of the array being held in the INDEX function.
In this case that would be: ROW($1:$990).

The logical test in the IF function returns the corresponding value from the
ROW($1:$990) function and that value in turn returns the corresponding value
from the INDEX array.

Biff