Thanks:  0
Likes:  0

# Thread: Validation list on separate worksheet

1. =OFFSET(\$C\$1,0,0,MATCH(REPT("z",255),\$C:\$C))
Hi Shane,

probably just as much to see if I *could* actually explain this in a manner that makes sense to someone, here's my full explanation:

Since the outer sets of brackets do things in order, we need to start with the first expression entirely contained within brackets (parentheses) :

REPT("z",255)

As Aladin has kindly explained, this repeats the character "z" 255 times (the largest text value allowable)

I'll refer to this text string from here as "zzzzzzz" for simplicity, just carry it on in your head 250ish times.

Plugging this into the next step :

MATCH("zzzzzzzzz",\$C:\$C)

this is asking excel to perform the MATCH function, in column C, looking for "zzzzzzzz" and return it's position in the column relative to the starting row. However, MATCH has 3 arguements : lookup value, range, match type.

We have omitted match type. So when this last arguement is ommitted, MATCH assumes it to be "1". When match type is "1", MATCH will find the largest value that is less than or equal to lookup_value.

I have to admit, I struggle a bit with this one, but have come to the conclusion that it returns the largest POSITION IN THE COLUMN that is less than or equal to the position in the column of the lookup value. I do hope I'm right on this otherwise I am totally confused as the help file doesn't make sense otherwise.

SO anyway, on the basis that we never encounter the text string of "zzzzzzzzzzzzz" in our everyday spreadsheets, it will default to finding the largest position in column A of a text string : ie the last row.

Assume our data covers 20 rows, this value will be 20.

We can therefore substitute this section of our formula with just "20" to get the following :

=OFFSET(\$C\$1,0,0,20)

which looks almost understandable !

The OFFSET command performs the following :

Starting at cell C1, moving 0 cells up or down and 0 cells left or right, return the range which is 20 cells down and [0] across... the [0] being assumed to be the same as the height or width as our cell C1 reference because we ommitted it as a 4th arguement

(excel assumes a lot)

so we end up with the range C1 to 20 rows down from C1...... with the pertinant number being 20 which is totally dependant on the result of our MATCH function looking for the last text cell in the same column....

As mentioned, we can also make it dynamic horizontally by NOT omitting the 4th arguement in OFFSET, but replacing it with yet another MATCH function which rather than searching column C, searches a particular row (say row 1 as that it the top left of our data) : thusly :

=OFFSET(\$C\$1,0,0,MATCH(REPT("z",255),\$C:\$C),MATCH(REPT("z",255),\$C\$1:\$AV\$1))

Obviously, this won't help in a named range for a data validation as it needs to be just a single column, but it's handy for databases, VLOOKUP ranges, print ranges etc etc

you can copy this into the "refers to" field of a named range before entering its actual name, to save typing it all out..

I'm done, it's bedtime, and I suspect Yogi is itching to rip this explanation to pieces

Hope this helps
Chris

2. OK I'm deep!!!!
My List starts at A22 (title only) because I have text above this area. I tried entering the info in and yes it works but it picks up the text from A1. I thought I would be smart and edit the string command to start at A23
=OFFSET(Cover!\$A\$23,0,0,MATCH(REPT("z",255),Cover!\$A:\$A))

NO LUCK. My validation cells on other sheets start at the first "blank" opening below my last list, so I have to scroll up to get to the data. I have the "ignore blank" enabled but that doesn't seem to help. So I tested it on the 1st sheet (Cover) with a validation and the same thing. I moved the list over to C: where no text was above and tried it and the validation on "Cover" where my list is had the text starting correctly, but about 10 blanks thereafter, and my other sheets still validate from the middle? Quite confusing. Can someone help with the string that will allow me to have text above my list, yet still let me use the validation pull down menu dynamically in other sheets? Could be a hair puller....well its been for me!
thanks

3. Hi Eire,

hmmmm

I just tried to replicate this "problem" and it works fine for me, even on other sheets...

hmmmm

can you highlight your named range "refers to" box and post it in your reply, I'm intrigued !

ta
Chris

4. As mentioned, we can also make it dynamic horizontally by NOT omitting the 4th arguement in OFFSET, but replacing it with yet another MATCH function which rather than searching column C, searches a particular row (say row 1 as that it the top left of our data) : thusly :

=OFFSET(\$C\$1,0,0,MATCH(REPT("z",255),\$C:\$C),MATCH(REPT("z",255),\$C\$1:\$AV\$1))

Obviously, this won't help in a named range for a data validation as it needs to be just a single column, but it's handy for databases, VLOOKUP ranges, print ranges etc etc
Hi Chris:
Thanks for a detailed explanation for picking up a dynamic range ... both row-wise and column-wise!

Regards

5. thanks Yogi.... there's another thread re this though as it looks like being in dispute !

6. On 2002-04-12 11:34, eire wrote:
OK I'm deep!!!!
My List starts at A22 (title only) because I have text above this area. I tried entering the info in and yes it works but it picks up the text from A1. I thought I would be smart and edit the string command to start at A23
=OFFSET(Cover!\$A\$23,0,0,MATCH(REPT("z",255),Cover!\$A:\$A))

NO LUCK. My validation cells on other sheets start at the first "blank" opening below my last list, so I have to scroll up to get to the data. I have the "ignore blank" enabled but that doesn't seem to help. So I tested it on the 1st sheet (Cover) with a validation and the same thing. I moved the list over to C: where no text was above and tried it and the validation on "Cover" where my list is had the text starting correctly, but about 10 blanks thereafter, and my other sheets still validate from the middle? Quite confusing. Can someone help with the string that will allow me to have text above my list, yet still let me use the validation pull down menu dynamically in other sheets? Could be a hair puller....well its been for me!
thanks
Change the formula to:

=OFFSET(Cover!\$A\$23,0,0,MATCH(REPT("z",255),Cover!\$A:\$A)-22,1)

22 takes into account that your actual data start at row 23.

I added 1 as the 4th arg of OFFSET to tell it that the range consists of a single column. If your data range counts more columns, adjust this number accordingly: For example, A to C houses the relevant range, then that number should be 3.

The MATCH(REPT("z",255),Cover!\$A:\$A) bit should used if column A-values consists of text values as opposed to date-values or just numbers.

7. Chris, sorry for the delay in acknowledging your explanation. **** good explanantion of the complete formula. I finally understand exactly how it works and I reckon I could adapt it to other scenarios. Thanks for the tutorial!

8. No worries Shane, but to be fair, I should also point you to Ian's comments which highlight what looks like being an error in the explanation

http://www.mrexcel.com/board/viewtop...c=4889&forum=2

9. hi guys,,,

i have define a name :names: to the list of names in sheetb and thru data validation i have inserted a drop down on sheet1. and called in the data from sheet2.. and it is working fine... but getting lots of blank space
i have gone thru all ur replies and solution,
i am little new with excel.. can u guide me where to insert this formula -=offset(), pls guide me further..

ur help will be really appreciated

regards
av

## User Tag List

#### Posting Permissions

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