=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
:wink:
Hope this helps
Chris
