Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
index / match function | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
index to a range of cells | Excel Worksheet Functions |