Results 1 to 11 of 11

Using INDIRECT to refer to INDEX's output

Threaded View

  1. #1
    Registered User
    Join Date
    12-27-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Using INDIRECT to refer to INDEX's output

    As part of generating a list of matching values in an INDEX list, I've had the idea of checking the list from the last matching value's position and continuing down the list from there. I know the INDEX returns a cell value, but is there some way to access that or force it to output it so that another cell can use an INDIRECT reference to see 'where that got to', as such?

    Let me explain in more detail. I'm building an interactive character sheet for a roleplaying game, in which all of the abilities that a character would have are listed on a hidden sheet. The INDEX function references this sheet, and searches the entire sheet for items that match the class of the character via the following formula: =INDEX('Hidden Sheet'!A1:F4,MATCH(B2,'Hidden Sheet'!C1:C4,0),1,1). Now, this cell returns the value of the first matching value it finds on the hidden sheet, which is fair enough.

    Now, INDEX returns a cell reference, right? So, the cell underneath should be able to do the following: =INDEX((INDIRECT(A150)):'Hidden Sheet'!F4,MATCH(B2,'Hidden Sheet'!C1:C4,0),1,1). In theory, this should get the location that the last cell was referred to (which will be in the A column) as the starting value of the array, and then continue from there. Instead, it accesses the cell and gets the value of the INDEX function, and not the cell reference, meaning it just returns a big, fat #REF! error rather than doing as it's told.

    Can anyone suggest how to either rewrite the formula, so that the cell underneath returns the next value that matches the criteria, or to allow me to force the cell to return the value I want, so that the cell underneath can reference that and use it to continue the search?
    Last edited by Libelnon; 12-28-2013 at 02:22 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA for creating Index-Match every 2 rows where INDEX refer to a different column per row
    By bisaya789 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2012, 03:46 AM
  2. Cannot refer to 'error' = #N/A cell output
    By heatwave in forum Excel General
    Replies: 4
    Last Post: 03-09-2011, 11:10 PM
  3. Using INDIRECT to refer to different workbooks
    By Govind in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 04:05 AM
  4. Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1