A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

lookup function



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 12:47 PM posted to microsoft.public.excel.worksheet.functions
Keith[_22_]
external usenet poster
 
Posts: 10
Default lookup function

I have a column listing numbers in worksheet B. I'm looking for a formula
to place in worksheet A that will list the bottom number listed in that
worksheet B column.

Any help would be much appreciated.

  #2  
Old June 2nd, 2010, 12:59 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default lookup function

Try the below..to retieve the last number

=LOOKUP(10^10,Sheet2!A:A)

--
Jacob (MVP - Excel)


"Keith" wrote:

I have a column listing numbers in worksheet B. I'm looking for a formula
to place in worksheet A that will list the bottom number listed in that
worksheet B column.

Any help would be much appreciated.

  #3  
Old June 3rd, 2010, 12:06 AM posted to microsoft.public.excel.worksheet.functions
Keith[_22_]
external usenet poster
 
Posts: 10
Default lookup function

Hi again,

I tried the formula you supplied but it didn't seem to work so I will
explain in a little more detail:

In a worksheet named '1' I have this heading in R11:

cum total cost / 100km




$3,030.97
$1,587.52
=IF(AND(Q18="",L18=""),"",Q18/(L18/100))


Then the above formula from R12:R3000.
Of course some cells don't display a $ value because of blank cells
elsewhere (IF formula)

In my 'MENU' worsheet, I need a formula that will pck up that last (or
bottom) cell in R11:R3000 which is displayed as a $ value.

Hope this helps.

Keith


"Jacob Skaria" wrote in message
...
Try the below..to retieve the last number

=LOOKUP(10^10,Sheet2!A:A)

--
Jacob (MVP - Excel)


"Keith" wrote:

I have a column listing numbers in worksheet B. I'm looking for a
formula
to place in worksheet A that will list the bottom number listed in that
worksheet B column.

Any help would be much appreciated.


  #4  
Old June 3rd, 2010, 12:25 AM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default lookup function

What did not work?

=LOOKUP(10^10,R:R) should do the trick.

If you anticipate a number greater than 10 billion in the range use 99^99
just to be safe.


Gord Dibben MS Excel MVP

On Thu, 3 Jun 2010 09:06:49 +1000, "Keith" wrote:

Hi again,

I tried the formula you supplied but it didn't seem to work so I will
explain in a little more detail:

In a worksheet named '1' I have this heading in R11:

cum total cost / 100km




$3,030.97
$1,587.52
=IF(AND(Q18="",L18=""),"",Q18/(L18/100))


Then the above formula from R12:R3000.
Of course some cells don't display a $ value because of blank cells
elsewhere (IF formula)

In my 'MENU' worsheet, I need a formula that will pck up that last (or
bottom) cell in R11:R3000 which is displayed as a $ value.

Hope this helps.

Keith


"Jacob Skaria" wrote in message
...
Try the below..to retieve the last number

=LOOKUP(10^10,Sheet2!A:A)

--
Jacob (MVP - Excel)


"Keith" wrote:

I have a column listing numbers in worksheet B. I'm looking for a
formula
to place in worksheet A that will list the bottom number listed in that
worksheet B column.

Any help would be much appreciated.


  #5  
Old June 4th, 2010, 11:32 AM posted to microsoft.public.excel.worksheet.functions
Keith[_22_]
external usenet poster
 
Posts: 10
Default lookup function

Many thanks, it has worked. What with we all do without Excel and its gurus.
Keith

"Gord Dibben" gorddibbATshawDOTca wrote in message
...
What did not work?

=LOOKUP(10^10,R:R) should do the trick.

If you anticipate a number greater than 10 billion in the range use 99^99
just to be safe.


Gord Dibben MS Excel MVP

On Thu, 3 Jun 2010 09:06:49 +1000, "Keith" wrote:

Hi again,

I tried the formula you supplied but it didn't seem to work so I will
explain in a little more detail:

In a worksheet named '1' I have this heading in R11:

cum total cost / 100km




$3,030.97
$1,587.52
=IF(AND(Q18="",L18=""),"",Q18/(L18/100))


Then the above formula from R12:R3000.
Of course some cells don't display a $ value because of blank cells
elsewhere (IF formula)

In my 'MENU' worsheet, I need a formula that will pck up that last (or
bottom) cell in R11:R3000 which is displayed as a $ value.

Hope this helps.

Keith


"Jacob Skaria" wrote in message
...
Try the below..to retieve the last number

=LOOKUP(10^10,Sheet2!A:A)

--
Jacob (MVP - Excel)


"Keith" wrote:

I have a column listing numbers in worksheet B. I'm looking for a
formula
to place in worksheet A that will list the bottom number listed in that
worksheet B column.

Any help would be much appreciated.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:20 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.