On Thu, 13 Oct 2005 10:43:43 -0700, "Conan Kelly" <CTBarbarin at msn dot com
wrote:

Hello all,

There are two things I'm trying to do.

First, is there a function that will return (to the cell the function
is in) the address of the maximum value in a range? I have a chart
based on months over several years. I have the last several rows of
the chart data left blank (for future input), but my chart includes
these blank months in it. I'm hoping to dynamically set the range of
the chart's source data based on the address that appears in a cell,
so the chart will only show the months that have data in them.

Second, is there a function that will extract unique values from a
list? Like Auto Filter does: When you turn Auto Filter on and click
the drop down in one of the columns, there are only unique values in
the drop down list.

If there is a way to create worksheet functions in VBA and then use
them in the cells of the worksheets, please post sample code.

Thanks for any help anyone can provide,

Conan Kelly


You can probably use some variation of the MATCH, MAX and ADDRESS or INDEX
functions depending on how your data is set up.

Here are some examples:

Find address of maximum number in row 2:

=ADDRESS(2,MATCH(MAX(2:2),2:2,0))

Find address of maximum number in Column B

=ADDRESS(MATCH(MAX(B:B),B:B,0),2)

With data in B2:B65535 and Dates in A2:A65535, return the date in column A that
corresponds with the last entry in column B:

If there are no blanks in the data:

=INDEX(A:A,MATCH(TRUE,ISBLANK(B2:B65535),0))

entered as an array formula (hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula).

If there may be blanks in the data, then try:

=INDEX(A:A,MAX(ISNUMBER(B2:B65535)*ROW(INDIRECT("2 :65535"))))

also entered as an array formula.



--ron