Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help - Not Sure Where to Post
Hey All, This is my first post so I'm not to sure where to put it, but here it goes: I have a problem with my worksheet that I am trying to create. I'm trying to build a sheet that helps me track my "fantasy" stocks. I want to build it myself b.c that's the type of person that I am. My questions is how to i program a cell to display the last cell of a specific column. Basially I want cell B1 to display whatever number is in the last column of H. Meaning if H7 has the last number in it I want B1 to display H7, but if H8 is the last cell filled I want B1 to display that. Is my question clear? Thanks for the help, -Tom -- tompal ------------------------------------------------------------------------ tompal's Profile: http://www.excelforum.com/member.php...o&userid=30405 View this thread: http://www.excelforum.com/showthread...hreadid=500732 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help - Not Sure Where to Post
tompal,
Try this: =OFFSET(H1,COUNTA(H:H)-1,0) This assumes that you have an entry in every cell in column H between the top and the bottom. Regards Colin "tompal" wrote in message ... Hey All, This is my first post so I'm not to sure where to put it, but here it goes: I have a problem with my worksheet that I am trying to create. I'm trying to build a sheet that helps me track my "fantasy" stocks. I want to build it myself b.c that's the type of person that I am. My questions is how to i program a cell to display the last cell of a specific column. Basially I want cell B1 to display whatever number is in the last column of H. Meaning if H7 has the last number in it I want B1 to display H7, but if H8 is the last cell filled I want B1 to display that. Is my question clear? Thanks for the help, -Tom -- tompal ------------------------------------------------------------------------ tompal's Profile: http://www.excelforum.com/member.php...o&userid=30405 View this thread: http://www.excelforum.com/showthread...hreadid=500732 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help - Not Sure Where to Post
Can't remember where I got this from but it works great!
=INDEX(B1:B100,MAX((B1:B1000)*ISNUMBER(B1:B100)*R OW(B1:B100)-ROW($B1))+1) this is an array formula so it needs to entered using Shift+Ctrl+Enter HTH JG "Colin Sandall" wrote: tompal, Try this: =OFFSET(H1,COUNTA(H:H)-1,0) This assumes that you have an entry in every cell in column H between the top and the bottom. Regards Colin "tompal" wrote in message ... Hey All, This is my first post so I'm not to sure where to put it, but here it goes: I have a problem with my worksheet that I am trying to create. I'm trying to build a sheet that helps me track my "fantasy" stocks. I want to build it myself b.c that's the type of person that I am. My questions is how to i program a cell to display the last cell of a specific column. Basially I want cell B1 to display whatever number is in the last column of H. Meaning if H7 has the last number in it I want B1 to display H7, but if H8 is the last cell filled I want B1 to display that. Is my question clear? Thanks for the help, -Tom -- tompal ------------------------------------------------------------------------ tompal's Profile: http://www.excelforum.com/member.php...o&userid=30405 View this thread: http://www.excelforum.com/showthread...hreadid=500732 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help - Not Sure Where to Post
Tom
=LOOKUP(9.99999999999999E+307,H:H) will fetch the last numeric value in col H Ignores blanks in the column. Gord Dibben MS Excel MVP On Thu, 12 Jan 2006 11:25:00 -0600, tompal wrote: Hey All, This is my first post so I'm not to sure where to put it, but here it goes: I have a problem with my worksheet that I am trying to create. I'm trying to build a sheet that helps me track my "fantasy" stocks. I want to build it myself b.c that's the type of person that I am. My questions is how to i program a cell to display the last cell of a specific column. Basially I want cell B1 to display whatever number is in the last column of H. Meaning if H7 has the last number in it I want B1 to display H7, but if H8 is the last cell filled I want B1 to display that. Is my question clear? Thanks for the help, -Tom |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help - Not Sure Where to Post
Good afternoon Gord. Would you mind explaining why you use that number?
I've seen it in formulae before, but don't have any idea why it is used. Thanks in advance. -- Sincerely, Michael Colvin "Gord Dibben" wrote: Tom =LOOKUP(9.99999999999999E+307,H:H) will fetch the last numeric value in col H Ignores blanks in the column. Gord Dibben MS Excel MVP On Thu, 12 Jan 2006 11:25:00 -0600, tompal wrote: Hey All, This is my first post so I'm not to sure where to put it, but here it goes: I have a problem with my worksheet that I am trying to create. I'm trying to build a sheet that helps me track my "fantasy" stocks. I want to build it myself b.c that's the type of person that I am. My questions is how to i program a cell to display the last cell of a specific column. Basially I want cell B1 to display whatever number is in the last column of H. Meaning if H7 has the last number in it I want B1 to display H7, but if H8 is the last cell filled I want B1 to display that. Is my question clear? Thanks for the help, -Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
post code on col1 & list of suburbs in col2 - what is the formula | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions |