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

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

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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

"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Biff
>
> Thanks for the help, my only problem now is, is that I get a #Num error
> 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
> Sonar
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

You are trying to reference outside of the range.

--

HTH

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


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"sonar" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=399751
>