+ Reply to Thread
Results 1 to 4 of 4

Dynamic Reference with Offset

  1. #1
    Registered User
    Join Date
    07-25-2013
    Location
    Lenexa, Kanas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Dynamic Reference with Offset

    I currently have a formula that uses on offset formula to return a value based on which column (month) that the value resides. I currently have hardcoded the line that needs to be returned and if the source data changes in size my formula will have to be manually updated.

    =IF(+Info!$B$8-11>0,-OFFSET('Q - Inc. Stmt. by Month_CY'!$F10,0,Info!$B$8-23,1,1),-OFFSET('Q - Inc. Stmt. by Month_PY'!$Q11,0,+Info!$B$8-11,1,1))

    Info!$B$8 equal current month (i.e. 6=June)

    I am using an If statement to switch between two data ranges based on if it is Prior Year or Current Year. (The result is a rolling twelve month financial)

    I have a GL Node (i.e. "1065" = "Net Income") that I would like to reference to allow for a dynamic result.

    I believe and Index Function would work, but I am unsure how to set it up.

    Thanks,

    For you assistance.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,178

    Re: Dynamic Reference with Offset

    Hi
    please post a sample sheet - Thx

  3. #3
    Registered User
    Join Date
    07-25-2013
    Location
    Lenexa, Kanas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Dynamic Reference with Offset

    Here is an example file for your review.

    Thanks
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-25-2013
    Location
    Lenexa, Kanas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Dynamic Reference with Offset

    Has anyone had a chance to look at my request? If more information is needed please let me know.

+ 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 Offset Ranges
    By Haggis29 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-24-2013, 07:18 AM
  2. Offset a cell using a dynamic (moving) reference cell
    By wavelet in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-01-2010, 09:10 AM
  3. OFFSET func dynamic "reference"
    By debio4ko in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-04-2007, 09:13 AM
  4. Dynamic Charts Offset
    By CSK in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-17-2006, 03:37 PM
  5. dynamic offset
    By michael.cambray@wellpoint.com in forum Excel General
    Replies: 3
    Last Post: 03-03-2005, 09:06 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