# Thread: Help with Offset formula

1. ## 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. 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.

3. 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
•