multiple commas in formual

[artjohnson]

I'm trying to get an understanding of this formula used in a routine that creates one validation list based on another. Can anyone tell me what the +1,2,,, portion of this formula does?

=OFFSET(INDIRECT(ADDRESS(MATCH(Val1Cell, Objects,0)+1,2,,,"Lists")),0,0,COUNTIF(Objects,Val1Cell),1)

 

[CircuitsMan]

Normally a function would have arguments between all commas, but if you want to use the formulas default values, then you can leave them blank. Hence, the ",,,".

It looks to me like this part occurs in the address function. From Excel's help file, the syntax is "ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])". Your function specifies values for row_num, col_num, and [sheet_text], but uses the default values for [abs_num] and [a1]. See Excel's help file on "Address Function" for more information.

 

[artjohnson]

Thanks, that helps a lot. I had trouble parsing this with consecutive OFFSET, INDIRECT, ADDRESS, and MATCH functions. I'd guess then that the default value for [abs_num] is True and that the format [a1] is also going to be True. Excel's help file on "Address Function" confirms this.