Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / March 2008

Tip: Looking for answers? Try searching our database.

index error

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SteveC - 13 Mar 2008 18:19 GMT
=INDEX('A2_Sensitivity Analysis'!$AJ$16:$BA$31,COLUMNS($A:I),ROWS($1:25))

This worked until I inserted a few rows on the worksheet 'A2_Sensitivity
Analysis'.  Now I get #REF! error.  I'm trying to reference cells in column
AV, in this case row 24 on 'A2_Sensitivity Analysis'.

The benefit of this formula is that I can drag it across, and it will
reference the other rows in the same column of worksheet A2_Sensitivity
Analysis.

I tried adjusting the formula, but since I don't really understand it I keep
getting #ref errors.  Any suggestions on how to modify it?  thanks very much.
Joel - 13 Mar 2008 20:29 GMT
Here is what happen

Index format is Index(Table Range, Row, Column).  The function returns a
value from a table.  the number of the Row or Column cannot exceed the size
of the table   Your code returns 9 for the number of rows (columns A:I) and
25 for the number of rows.  The size of the table is 16 rows ad 18 columns
(AJ to BA).  You have the following

Index (Table 16 rows by 18 columns,  Row 9, Column 25).  25 is larger than
18 so you get an error.

The problem was caused by the Table referencing sheet 'A2_Sensitivity
Analysis' and the columns not referencing a sheet.  You code should of looked
like this

=INDEX('A2_Sensitivity Analysis'!$AJ$16:$BA$31,'A2_Sensitivity
Analysis'!COLUMNS($A:I),'A2_Sensitivity Analysis'!ROWS($1:25))

But this causes an error.  When you added columns you changged the size of
the Table but the number of rows and columns did not change.

> =INDEX('A2_Sensitivity Analysis'!$AJ$16:$BA$31,COLUMNS($A:I),ROWS($1:25))
>
[quoted text clipped - 8 lines]
> I tried adjusting the formula, but since I don't really understand it I keep
> getting #ref errors.  Any suggestions on how to modify it?  thanks very much.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.