Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Help with Offset formula

    Hi Loungers

    I'm after some assistance with the following offset formula

    =OFFSET(Sheet2!$K$7:$K$18,0,MATCH(Sheet1!$E12,type ,0))

    This works fine and offsets as it should, but i'm after help with two modifications.
    1. When offseting it looks in every second colum, ie starting at L then N,P,R,T etc
    2. Imbed (or what ever) an offset to count the number of populated cells in a particular colum, so when the the list in the Data Validation is actived the list is limited to the number of populated cells.

    I know how to develop an offset forumla to do this, but not sure how to use it in conjunction with the formula above.

    I hope this make sence. Any thoughs/suggestions/examples would be much appreciated.

    Regards

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    For modification (1) presumably you can just use

    =OFFSET(Sheet2!$K$7:$K$18,0,MATCH(Sheet1!$E12,type ,0)*2-1)

    which will return results from l, n, p etc.

    As for modification (2) I'm guessing you're using the offset to generate a validation list for a celll somewhere. At present your list is 12 rows, i.e. the height of the range in the first argument. So to give it a variable height you can use the optional height argument, wrapping into it the same offset function and counting the populated cells:

    =OFFSET(Sheet2!$K$7:$K$18,0,MATCH(Sheet1!$E12,type ,0)*2-1,COUNTA(OFFSET(Sheet2!$K$7:$K$18,0,MATCH(Sheet1!$ E12,type ,0)*2-1)))

    Does that do the job?

    Ian.

    EDIT: Missed a factor, corrected. Also, if you are looking in every other column because those columns contain formulas dependent on the non-selected columns then COUNTA won't work because all the cells are populated, you might need COUNTIF or something.
    Last edited by iansavell; 2013-05-08 at 05:43.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Ian - Thanks very much for your help.

    Will give your it a shot tomorrow when I get back to the office.

    Will let you know

    Regards

Posting Permissions

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