+ Reply to Thread
Results 1 to 3 of 3

Offset and Indirect with Dynamic Named Ranges

  1. #1
    Registered User
    Join Date
    06-19-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    30

    Offset and Indirect with Dynamic Named Ranges

    Hi

    I would be grateful for some help, I understand that OFFSET and INDIRECT do not work when using a dynamic named range, can you please with a solution.

    Attached is a spreadsheet with the month-end prices for four stocks (worksheet 1), the second worksheet shows the monthly gain/loss for each stock and a table with the correlation of each.

    When I use the CORREL function with a static named range (not dynamic), the INDIRECT works fine, although wont work with a dynamic range. i.e. =CORREL(INDIRECT(H$6),INDIRECT($G7))

    Very frustrating!!!

    Much appreciated

    Nick
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-21-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    227

    Re: Offset and Indirect with Dynamic Named Ranges

    Hi,
    [Edited]
    You may have infinitely long Non-Dynamic ranges as a work around. Instead of your NDCoke range and other ND ranges to be limited to 100+ cells, make it 10000 something like that..

    ~Regards
    Last edited by sakmsb; 09-07-2014 at 11:09 PM.

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Offset and Indirect with Dynamic Named Ranges

    This may be a possible workaround...
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    check the attached file...
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Dynamic Named Ranges and Indirect Data validation INCOMPATIBLE?
    By css0911 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-08-2013, 06:17 AM
  2. Replies: 14
    Last Post: 10-02-2013, 07:39 AM
  3. Using INDIRECT with named ranges
    By wunderfisch in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-17-2013, 01:10 PM
  4. 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
  5. INDIRECT and named ranges
    By Excel_Learner42 in forum Excel General
    Replies: 3
    Last Post: 01-02-2009, 01:18 PM

Tags for this Thread

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