- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel help request
Posted on 2/16/20 at 1:51 pm
Posted on 2/16/20 at 1:51 pm
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!
13
Posted on 2/16/20 at 1:52 pm to GeneralLee
huh?
Posted on 2/16/20 at 1:55 pm to GeneralLee
Not sure I can find a formula, but I know a VBA code you could use
Posted on 2/16/20 at 1:56 pm to VermilionTiger
VBA is not an option unfortunately.
This post was edited on 2/16/20 at 2:02 pm
Posted on 2/16/20 at 1:57 pm to GeneralLee
Try control alt delete. If that doesn't work unplug your computer then plug it back in
Posted on 2/16/20 at 1:57 pm to VermilionTiger
Looks like another formula option could be using Index and CountA functions?
Posted on 2/16/20 at 1:58 pm to GeneralLee
Tech board might be able to help you quicker 
Posted on 2/16/20 at 1:58 pm to GeneralLee
Let me think for a bit
Posted on 2/16/20 at 1:59 pm to GeneralLee
Treefiddy
Posted on 2/16/20 at 2:00 pm to GeneralLee
It might be quicker to just type 206 instead of jumping through all these hoops.
Posted on 2/16/20 at 2:01 pm to VermilionTiger
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.
Posted on 2/16/20 at 2:01 pm to GeneralLee
It won’t.
Posted on 2/16/20 at 2:01 pm to GeneralLee
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
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
Posted on 2/16/20 at 2:01 pm to GeneralLee
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.
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/20 at 2:06 pm
Posted on 2/16/20 at 2:02 pm to GeneralLee
Have you checked your 1D 10 T file?
Posted on 2/16/20 at 2:03 pm to OysterPoBoy
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.
Posted on 2/16/20 at 2:05 pm to GeneralLee
quote:
VBA is not an option unfortunately
How?
Posted on 2/16/20 at 2:05 pm to Bullfrog
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!
Posted on 2/16/20 at 2:13 pm to GeneralLee
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.
Posted on 2/16/20 at 2:15 pm to GeneralLee
I still don't know what you are looking to accomplish here
Page 1 of 2
Back to top
Follow TigerDroppings for LSU Football News