Consulting

Results 1 to 8 of 8

Thread: Worksheet formula Offset 1 Column Left & another Cell

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    347
    Location

    Worksheet formula Offset 1 Column Left & another Cell

    How do I write a Worksheet formula which references 1 column to the left & includes !FK2 to the result

    For example, The column to the left is Usually the Months,
    and I need the formula to the right of the month to be like =January!FK2

    Any ideas appreciated.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,008
    Location
    Use INDIRECT()

    =INDIRECT(OFFSET(B1,0,-1)&"!FK1")
    Capture.JPG

    Where the FK1 cell on 'Janurary' = 15
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    347
    Location
    Hi Paul_Hossler:

    That worked like a charm. Thanks

    But, Is there anyway that Excel can be coaxed on a worksheet to allow you to fill down the original first formula,
    =January!FK2
    but rather than increment the FK2 portion of the formula to FK3 etc..etc...
    instead, it increments the month portion to February, March, etc...etc... or anything left of the !

  4. #4
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    469
    Location
    Hi simora!
    Please confirm attachments.
    Attached Files Attached Files

  5. #5
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    347
    Location
    Hi 大灰狼1976 :


    The test.xlsx sheet provided a great example and it worked.
    But in the formula,
    I'm stumped as to the actual function of the 4 at the end of the formula =INDIRECT($A1&"!"&ADDRESS(1,COLUMN(A1),4))
    I don't see it referenced in the docs that I've been looking at, and changing it did not alter the results.
    Exactly what is it used for ?
    Thanks.

  6. #6
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    469
    Location
    ADDRESS(row_num,column_num,abs_num,a1,sheet_text)


    ABS_num Reference type returned
    1 or omit absolute reference
    2 row = absolute,column = relative
    3 row = relative,column = absolute
    4 relative reference


    There are differences when dragging the formula.

  7. #7
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    469
    Location
    Sorry! My explanation is incorrect.
    for example: =INDIRECT($A1&"!"&ADDRESS(1,COLUMN(A1),4))
    COLUMN(A1) is relative reference, so the type of ADDRESS() will not affect the result.

  8. #8
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    347
    Location
    Hi 大灰狼1976:

    Got it. I didn't see that bit about the type returned.

    Thank You.

Posting Permissions

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