+ Reply to Thread
Results 1 to 7 of 7

Dynamic list with Offset and indirect

Hybrid View

  1. 02-08-2016, 06:57 AM #1
    Captainjay
    Captainjay is offline
    Registered User
    Join Date
    02-08-2016
    Location
    Northumberland, England
    MS-Off Ver
    Office 13
    Posts
    3

    Dynamic list with Offset and indirect

    Hi All,

    Im really hoping someone can help, this issue has been bugging the life out of me for weeks now!!

    Ok so i have quite a complex workbook that I have been developing for a while now. Im pretty good with Excel so i havent encountered too many issues so far, however i have come across the most frustrating thing ever! My work book uses alot of dynamic lists which I have no issues with. My issues come when I try and use indirect. seems simple I know but let me explain.

    The workbook has sheets Monday - Sunday with a Weekly roll up sheet and a data sheet. all the information for my lists etc are on the data sheet. on the daily sheets I have 3 columns that relate to each other. B:C (merged) has a heading of "Activity", D:F (merged) has a heading or "Workload" and G:I (merged) is headed Task.
    On the Data sheet I have a table with the Workloads across the top and the tasks running down the left. Not every Workload has all of the tasks in it, so i have seperate dynamic lists that only count the tasks that are done under that workload. I have used {=IFERROR(INDEX($P$2:$P$26,MATCH(0,IF(ISBLANK($P$2:$P$26),"",COUNTIF(Q$1:$Q1,$P$2:$P$26)),0)),"")} which works perfectly and takes out all the blank spaces.

    The problem now arises when I attempt to Indirect link between Workloads and Tasks on the daily pages. Because tasks can be added and removed to workloads, to save me time later down the line that is why I used the above formula as there are several blank cells that come up at the bottom of the lists that i dont want to appear in my dynamic list. If you notice the range needs to cover cells 2:26 no matter which column i put the information into.
    I tried to use =OFFSET(data!$Q$2,0,0,SUMPRODUCT(--(data!$Q$2:$Q$26<>"")),1) when naming a range. However, when I go onto (say Monday) and use the indirect validation to link the Task column to the Workload column I get an error message "The Source Currently equates to an error. Do you want to continue". I know this is because there is currently no data in the Workload Column, so when I select Yes, then put an entry into worklist column nothing will come up as an available selection under Task.
    I have named the ranges exactly to the name that appears in Workload so I am stumpted as to why this is not working. I know the offset works as if I create a dynamic list that relates to one of the named tasks it brings up only the tasks in that list and ignores all of the blank cells.

    Any help would be massively appreciated with this issue!!

    thanks
    Reply With QuoteRegister To Reply
  2. 02-08-2016, 07:15 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,085

    Re: Dynamic list with Offset and indirect

    Try this to create your Dynamic Named Range:

    In the instance, the DNR, nrList is defined as:
    Formula: copy to clipboard 
    =Sheet2!$A$2:INDEX(Sheet2!$A:$A,MAX(MATCH(REPT("z",26),Sheet2!$A:$A,1),MATCH(9.999+35,Sheet2!$A:$A,1)))



    A
    B
    C
    D
    E
    1
    List (with spaces)
    2
    a
    f
    =INDEX($A:$A,MATCH(REPT("z",26),$A:$A,1))
    3
    b
    3
    =INDEX($A:$A,MATCH(9.999+35,$A:$A,1))
    4
    c
    f
    =INDEX($A:$A,MAX(MATCH(REPT("z",26),$A:$A,1),MATCH(9.999+35,$A:$A,1)))
    5
    nrList
    =Sheet2!$A$2:INDEX(Sheet2!$A:$A,MAX(MATCH(REPT("z",26),Sheet2!$A:$A,1),MATCH(9.999+35,Sheet2!$A:$A,1)))
    6
    1
    6
    =SUM(nrList)
    7
    2
    8
    3
    9
    10
    d
    11
    e
    12
    f
    13



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 02-08-2016, 07:37 AM #3
    Captainjay
    Captainjay is offline
    Registered User
    Join Date
    02-08-2016
    Location
    Northumberland, England
    MS-Off Ver
    Office 13
    Posts
    3

    Re: Dynamic list with Offset and indirect

    Thanks TMS,

    Unfortunately that didnt work. When I altered the details to match the cells the data should pull from i get "You may not use reference operators (such as unions, intersections, and ranges) or array constants for data validation criteria" error message
    Reply With QuoteRegister To Reply
  4. 02-08-2016, 08:57 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,085

    Re: Dynamic list with Offset and indirect

    Forgot, unique entries, excluding spaces, Array Formula:

    Formula: copy to clipboard 
    =IFERROR(INDEX($A$2:$A$100, MATCH(0, IF(ISBLANK($A$2:$A$100), 1, COUNTIF(B1:$B$1, $A$2:$A$100)), 0)),"")
    committed with Ctrl-Shift-Enter rather than just Enter
    Reply With QuoteRegister To Reply
  5. 02-08-2016, 11:41 AM #5
    Captainjay
    Captainjay is offline
    Registered User
    Join Date
    02-08-2016
    Location
    Northumberland, England
    MS-Off Ver
    Office 13
    Posts
    3
    Sorry, i dont think i made myself clear. im using data validation to create my lists.
    Reply With QuoteRegister To Reply
  6. 02-08-2016, 08:55 AM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,085

    Re: Dynamic list with Offset and indirect


    A
    B
    C
    D
    1
    List (with spaces)
    List (without spaces)
    2
    a
    a
    f
    =INDEX($A:$A,MATCH(REPT("z",26),$A:$A,1))
    3
    b
    b
    4
    =INDEX($A:$A,MATCH(9.999+35,$A:$A,1))
    4
    c
    c
    4
    =INDEX($A:$A,MAX(MATCH(REPT("z",26),$A:$A,1),MATCH(9.999+35,$A:$A,1)))
    5
    1
    nrList
    =Sheet2!$A$2:INDEX(Sheet2!$A:$A,MAX(MATCH(REPT("z",26),Sheet2!$A:$A,1),MATCH(9.999+35,Sheet2!$A:$A,1)))
    6
    1
    2
    10
    =SUM(nrList)
    7
    2
    3
    11
    =COUNT(B:B)+COUNTIFS(B:B,">=A",B:B,"<=z")
    8
    3
    d
    9
    e
    4
    =INDEX($B:$B,COUNT(B:B)+COUNTIFS(B:B,">=A",B:B,"<=z"))
    10
    d
    f
    4
    =INDEX($B:$B,MATCH(9.999+35,$B:$B,1))
    11
    e
    4
    nrList2
    =Sheet2!$B$2:INDEX(Sheet2!$B:$B,COUNT(Sheet2!A:A)+COUNTIFS(Sheet2!A:A,">=A",Sheet2!A:A,"<=z"))
    12
    f
    13
    4
    14
    2
    DV: =nrList
    15
    b
    DV: =nrList2
    16
    Reply With QuoteRegister To Reply
  7. 02-08-2016, 11:52 AM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,085

    Re: Dynamic list with Offset and indirect

    i dont think i made myself clear.
    True.
    im using data validation to create my lists.
    No idea what that means.

    The problem here is that you know what you're looking at, what your workbook, worksheets, data, formulae, etc., look like, and what your process is for entering data. And nobody else does.

    I'm trying to second guess what you are trying to do, and obviously not succeeding.
    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)

Similar Threads

  1. Indirect search criteria to dynamic list
    By T86157 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-23-2015, 12:29 PM
  2. Offset and Indirect with Dynamic Named Ranges
    By nickmangan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2014, 10:42 PM
  3. Indirect Vlookup List and Dynamic Named Ranges - Is there a workaround
    By Jennasis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2013, 06:57 AM
  4. [SOLVED] OFFSET formula for a Dynamic list
    By JO505 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-10-2013, 05:11 PM
  5. dynamic list using offset and indirect
    By ncarrocino in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-07-2013, 05:11 PM
  6. Using LEFT with OFFSET to get a dynamic drop down list
    By alcopoppa in forum Excel General
    Replies: 6
    Last Post: 01-20-2012, 08:53 AM

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