Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Sheet Offset function (Excel 00)

    Hi Lounge,

    any one know how I can make my offset function use a sheet name typed or pick in a drop down menu?

    =Offset('test1'!B1,0,8)

    in this case test1 is select in the drop down.


    =Offset('test2'!B1,0,8)

    if Test 2 was selected

    I would like to find out how the make the sheet change

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Sheet Offset function (Excel 00)

    Say that the sheet name is in cell H32:
    <code>
    =OFFSET(INDIRECT("'"&H32&"'!B1"),0,8)
    </code>
    Cell H32 can show a dropdown list through Data | Validation.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Sheet Offset function (Excel 00)

    Thanks Hans,

    Would you by any chance know where I can find the post on generating a list of all the sheets in my workbook?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Sheet Offset function (Excel 00)

    See the thread starting at <post:=568,856>post 568,856</post:> or <post:=493,883>post 493,883</post:>.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Sheet Offset function (Excel 00)

    Thank you very much!

    one last thing,

    =OFFSET(INDIRECT("'"&H32&"'!B1"),0,8)

    how do I change the formula to be dynamic, meaning that when I drag it down it offsets down 1 row and when dragging to the left offsets a 1 column

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Sheet Offset function (Excel 00)

    Do you mean that you want the offsets 0 and 8 to change?

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Sheet Offset function (Excel 00)

    reply yes would i use row() and column()

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Sheet Offset function (Excel 00)

    Yep, instead of 0 you would use something like ROW()-15 where 15 is the row number of the cell containing the original formula, so in row 16 it evaluates to 16-15 = 1 etc. Similar for the column offset.

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Sheet Offset function (Excel 00)

    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
  •