Posted by
Message
GeneralLee
LSU Fan
Member since Aug 2004
9490 posts

Excel help request
I am looking for an Excel formula to pull up the last number in a series of cells that is not blank. For example, let's say I have five cells, and the numbers may be : 200, 202, 204, 206, and then a blank cell. I want a formula to pull up the 206 value. I am aware of lookup fuctions that can do this, for example, lookup(2,1/isnumber(cell references), cell references), but this formula is causing problems when put into a database, so I need another formula that would work. Any ideas? Thanks in advance!


Bayou
LSU Fan
Arizona Desert
Member since Feb 2005
31424 posts

re: Excel help request
huh?


Replies (0)
Replies (0)
15
VermilionTiger
LSU Fan
Member since Dec 2012
30234 posts

re: Excel help request
Not sure I can find a formula, but I know a VBA code you could use


GeneralLee
LSU Fan
Member since Aug 2004
9490 posts

re: Excel help request
VBA is not an option unfortunately.
This post was edited on 2/16 at 2:02 pm


little billy
LSU Fan
Orange County, CA
Member since May 2015
6116 posts

re: Excel help request
Try control alt delete. If that doesn't work unplug your computer then plug it back in


Replies (0)
Replies (0)
281
GeneralLee
LSU Fan
Member since Aug 2004
9490 posts

re: Excel help request
Looks like another formula option could be using Index and CountA functions?


The Nino
Atlanta United Fan
Member since Jan 2010
19797 posts

re: Excel help request
Tech board might be able to help you quicker


Replies (0)
Replies (0)
00
VermilionTiger
LSU Fan
Member since Dec 2012
30234 posts

re: Excel help request
Let me think for a bit


beerJeep
LSU Fan
Louisiana
Member since Nov 2016
22944 posts
 Online 

re: Excel help request
Treefiddy


Replies (0)
Replies (0)
11
OysterPoBoy
LSU Fan
City of St. George
Member since Jul 2013
17908 posts
 Online 

re: Excel help request
It might be quicker to just type 206 instead of jumping through all these hoops.


GeneralLee
LSU Fan
Member since Aug 2004
9490 posts

re: Excel help request
Also, the "blank" cells in my example are not really blank, they are set to equal "". So Index(A:A,CountA(A:A) formula would work if the last cell was really blank, but unfortunately that formula won't work as Excel is reading it as not being completely blank.


Replies (0)
Replies (0)
00
pjab
LSU Fan
NOLA/Denver
Member since Mar 2016
4165 posts

re: Excel help request
It won’t.


Replies (0)
Replies (0)
00
OweO
LSU Fan
Plaquemine, La
Member since Sep 2009
86236 posts
 Online 

re: Excel help request
Its not going to come up if it is with other information in a cell.

If you have 200, 202, 204, 206 in one sell you would have to search for "200, 202, 204, 206".

That's with the find and select option, but I am not sure if you can do what you are trying to do.

It seems like you are not using excel the way it is designed to be used


Replies (0)
Replies (0)
04
Bullfrog
LSU Fan
but I left before halftime.
Member since Jul 2010
44932 posts

re: Excel help request
If the number is always the largest, just use the Max formula.

I’m not on a computer but you should be able to google the formula to find the blank cell in a Column then lookup the relative position by one row and return that value.
This post was edited on 2/16 at 2:06 pm


OweO
LSU Fan
Plaquemine, La
Member since Sep 2009
86236 posts
 Online 

re: Excel help request
Have you checked your 1D 10 T file?


Replies (0)
Replies (0)
16
GeneralLee
LSU Fan
Member since Aug 2004
9490 posts

re: Excel help request
quote:

It might be quicker to just type 206 instead of jumping through all these hoops


I was just using simple math in my example. This is for a sheet with hundreds of columns of data, when I'm using monthly projections for five years, then switching to annual projections in year 6. But i have to run out the last year with months through the end of that calendar year, so I could have one to 12 "blank" months in that last calendar year depending on when my starting month is.


Sun God
LSU Fan
Member since Jul 2009
21012 posts

re: Excel help request
quote:

VBA is not an option unfortunately

How?


Replies (0)
Replies (0)
00
GeneralLee
LSU Fan
Member since Aug 2004
9490 posts

re: Excel help request
quote:

It the number is always the largest, just use the Max formula.


Not always the case with my data, otherwise this would be a welcome easy fix!


GeneralLee
LSU Fan
Member since Aug 2004
9490 posts

re: Excel help request
Looks like vlookup(9.99999999999999E+307 (i.e. a massive number, array,1) is my best option for the columns with numbers. Still noodling on how to come up with formula for columns with text.


Replies (0)
Replies (0)
00
VermilionTiger
LSU Fan
Member since Dec 2012
30234 posts

re: Excel help request
I still don't know what you are looking to accomplish here


first pageprev pagePage 1 of 2next pagelast page

Back to top

logoFollow TigerDroppings for LSU Football News
Follow us on Twitter, Facebook and Instagram to get the latest updates on LSU Football and Recruiting.

FacebookTwitterInstagram