Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
sonar
 
Posts: n/a
Default 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


+-------------------------------------------------------------------+
|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

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

You are trying to reference outside of the range.

--

HTH

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


"sonar" wrote in
message ...

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



  #4   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in
message ...

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



  #5   Report Post  
sonar
 
Posts: n/a
Default


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



  #6   Report Post  
Biff
 
Posts: n/a
Default

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('3 DBC'!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('3 DBC'!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" wrote in
message ...

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



  #7   Report Post  
sonar
 
Posts: n/a
Default


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

  #8   Report Post  
sonar
 
Posts: n/a
Default


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
:)


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399751

  #9   Report Post  
Biff
 
Posts: n/a
Default

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. Whe

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" wrote in
message ...

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I NEED HELP with the SPELLNUMBER Function vag Excel Worksheet Functions 0 June 21st 05 08:17 AM
Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM
index / match function Lisa Excel Worksheet Functions 3 April 1st 05 05:03 AM
Need a ISWorkday Function -- Any Ideas Mark Excel Worksheet Functions 5 March 29th 05 01:58 AM
index to a range of cells Frank Kabel Excel Worksheet Functions 0 October 27th 04 05:39 PM


All times are GMT +1. The time now is 09:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"