+ Reply to Thread
Results 1 to 2 of 2

Alternative to INDIRECT with Dependent Lists

  1. 12-05-2007, 02:48 PM #1
    RDSProgrammer
    RDSProgrammer is offline
    Registered User
    Join Date
    06-25-2007
    Posts
    20

    Question Alternative to INDIRECT with Dependent Lists

    I have two dropdowns on Sheet1. The first dropdown (A1) gets its values from a list defined by a named range. All of these values refer to named ranges.

    The second dropdown gets its values from a list using the INDIRECT function based on the first dropdown -- INDIRECT(A1). Thus, the second dropdown refers to the appropriate named range for its values.

    My problem is that our Workbook must operate with users that have both Excel and OpenOffice Calc. OpenOffice Calc does not support the INDIRECT function, as I have used it above.

    Is there an alternative to INDIRECT that I can use here that would work in both spreadsheets?
    Reply With QuoteRegister To Reply
  2. 12-05-2007, 03:10 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    You can perhaps use the Choose/Match combo to do this? Not sure if OO allows this one either


    =CHOOSE(MATCH(A1,Category,0),Cat1,Cat2)

    where A1 is where your first Dropdown menu exists.

    Category is the named range for this first dropdown...it is assumed the list for the range starts in row 1 if not, you will need to offset by add the number of rows above the list to the Match() function...e.g. Match(A1,Category,0)+2 to add 2 rows.

    Cat1 and Cat2 are the named subcategory ranges that appear in the 1st dropdown
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1