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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|