Thanks Ron
You just switched the light on!

"Ron Coderre" wrote:

Regarding =LOOKUP(10^99,Sheet2!B:B)

LOOKUP has a nice little quirk....
When the search_value is larger than any other value in the lookup range, it
simply returns the last item in the list that is the same type as the lookup
value (text vs numeric). In our case, that number is the Total Average that
you are looking for.

To ensure that there could not possibly be a match, many people use the
largest numeric value that Excel can handle (9.9999999999E-307). IMHO that's
only necessary in some scientific applications (astronomy for example), but
for my purposes, 10 to the 99th power is guarranteed to be larger than any
number I'll be looking for, plus it requires less typing and is cleaner
looking.

To find the last text value in Col_A, you'd use this variation:
=LOOKUP(REPT("z",255),A:A)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"DaveM" wrote:

Hi Ron

This also works thank you.

But I am not too sure how the formula works - I don't understand what it is
telling Excel to do.

What does '10^99' mean? I presume this means go to the last value in that
column
but I don't understand why it works and before I use the formula it would be
nice to know if there are any limitations to its use.

Thanks
Dave
"Ron Coderre" wrote:

Try this:

With:
Your list in columns A:B on Sheet2
NO other items in A:B except for the list

Then....
In a cell on Sheet1:
=LOOKUP(10^99,Sheet2!B:B)
That formula returns the last numeric item from Col_B on Sheet2

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"DaveM" wrote:

Hi,

I have a 2 columns of data that is downloaded from an extrernal source. The
list varies in length. At the bottom of the entries in column B is a cell
that calculates the average of all the entries above. The cell to the left is
titled 'Total Average (xxx)' where xxx is the number of entries.

I need to link to the Total Average figure from another worksheet.

I have been using the Index, Match function elsewhere. But because xxx
varies this is not possible.

Can I somehow use the Left function with Index Match such that only Total
Average is the Matched variable? And if so how do I write the formula for
this?

Or can somebody suggest an alternative method?

Thanks

Dave