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




CELL EQUALS LAST NONBLANK CELL IN A COLUMN
Can someone give me a function to use so that the cell at the bottom of a
column equals the last nonblank cell above in that column? 
Ads 
#2




CELL EQUALS LAST NONBLANK CELL IN A COLUMN
The best solution to this depends on many conditions. Among those a
What type of data is in the range, is it text or numeric, or can it be both? Are there any empty/blanks cells *within* the range? Are there any formulas in the range that might return formula blanks? As a starting point you can try something like this. Assumes no empty/blank cells within the range: =IF(COUNTA(A1:A10),INDEX(A1:A10,COUNTA(A1:A10)),"" )  Biff Microsoft Excel MVP "Vicky" > wrote in message ... > Can someone give me a function to use so that the cell at the bottom of a > column equals the last nonblank cell above in that column? 
#3




CELL EQUALS LAST NONBLANK CELL IN A COLUMN
There are formulas in the cells above. The formulas results in a number
based upon the "If" condition in the formulas, else it returns a blank value in the cell. Here is a sample of one of the formulas in a cell in the column above... =IF(AND(W16<>"",(COUNTA($W$3:W15)>0)(COUNTBLANK($W$3:W15)),(COUNTA($W17:W$22)=0)COUNTBLANK($W17:W$22)),$F16,"") "T. Valko" wrote: > The best solution to this depends on many conditions. Among those a > > What type of data is in the range, is it text or numeric, or can it be both? > Are there any empty/blanks cells *within* the range? > Are there any formulas in the range that might return formula blanks? > > As a starting point you can try something like this. Assumes no empty/blank > cells within the range: > > =IF(COUNTA(A1:A10),INDEX(A1:A10,COUNTA(A1:A10)),"" ) > >  > Biff > Microsoft Excel MVP > > > "Vicky" > wrote in message > ... > > Can someone give me a function to use so that the cell at the bottom of a > > column equals the last nonblank cell above in that column? > > > 
#4




CELL EQUALS LAST NONBLANK CELL IN A COLUMN
This will return the *last numeric value* in the range:
=IF(COUNT(A1:A10),LOOKUP(1E100,A1:A10),"")  Biff Microsoft Excel MVP "Vicky" > wrote in message ... > There are formulas in the cells above. The formulas results in a number > based upon the "If" condition in the formulas, else it returns a blank > value > in the cell. Here is a sample of one of the formulas in a cell in the > column > above... > > =IF(AND(W16<>"",(COUNTA($W$3:W15)>0)(COUNTBLANK($W$3:W15)),(COUNTA($W17:W$22)=0)COUNTBLANK($W17:W$22)),$F16,"") > > "T. Valko" wrote: > >> The best solution to this depends on many conditions. Among those a >> >> What type of data is in the range, is it text or numeric, or can it be >> both? >> Are there any empty/blanks cells *within* the range? >> Are there any formulas in the range that might return formula blanks? >> >> As a starting point you can try something like this. Assumes no >> empty/blank >> cells within the range: >> >> =IF(COUNTA(A1:A10),INDEX(A1:A10,COUNTA(A1:A10)),"" ) >> >>  >> Biff >> Microsoft Excel MVP >> >> >> "Vicky" > wrote in message >> ... >> > Can someone give me a function to use so that the cell at the bottom of >> > a >> > column equals the last nonblank cell above in that column? >> >> >> 
#5




CELL EQUALS LAST NONBLANK CELL IN A COLUMN
Thank you so much! This worked. I have yet to understand how LOOKUP works.
Maybe in time I will. :) "T. Valko" wrote: > This will return the *last numeric value* in the range: > > =IF(COUNT(A1:A10),LOOKUP(1E100,A1:A10),"") > >  > Biff > Microsoft Excel MVP > > > "Vicky" > wrote in message > ... > > There are formulas in the cells above. The formulas results in a number > > based upon the "If" condition in the formulas, else it returns a blank > > value > > in the cell. Here is a sample of one of the formulas in a cell in the > > column > > above... > > > > =IF(AND(W16<>"",(COUNTA($W$3:W15)>0)(COUNTBLANK($W$3:W15)),(COUNTA($W17:W$22)=0)COUNTBLANK($W17:W$22)),$F16,"") > > > > "T. Valko" wrote: > > > >> The best solution to this depends on many conditions. Among those a > >> > >> What type of data is in the range, is it text or numeric, or can it be > >> both? > >> Are there any empty/blanks cells *within* the range? > >> Are there any formulas in the range that might return formula blanks? > >> > >> As a starting point you can try something like this. Assumes no > >> empty/blank > >> cells within the range: > >> > >> =IF(COUNTA(A1:A10),INDEX(A1:A10,COUNTA(A1:A10)),"" ) > >> > >>  > >> Biff > >> Microsoft Excel MVP > >> > >> > >> "Vicky" > wrote in message > >> ... > >> > Can someone give me a function to use so that the cell at the bottom of > >> > a > >> > column equals the last nonblank cell above in that column? > >> > >> > >> > > > 
#6




CELL EQUALS LAST NONBLANK CELL IN A COLUMN
You're welcome. Thanks for the feedback!
How LOOKUP works: The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. To ensure that the lookup_value is greater than all the values in the lookup_vector we use an arbitrary number that is guaranteed to meet this condition. In this case the lookup_value is 1E100, 1 followed by 100 zeros. There's a pretty good chance that no number in your range will be greater than that so the formula returns the *last numeric* value in the range.  Biff Microsoft Excel MVP "Vicky" > wrote in message ... > Thank you so much! This worked. I have yet to understand how LOOKUP > works. > Maybe in time I will. :) > > "T. Valko" wrote: > >> This will return the *last numeric value* in the range: >> >> =IF(COUNT(A1:A10),LOOKUP(1E100,A1:A10),"") >> >>  >> Biff >> Microsoft Excel MVP >> >> >> "Vicky" > wrote in message >> ... >> > There are formulas in the cells above. The formulas results in a >> > number >> > based upon the "If" condition in the formulas, else it returns a blank >> > value >> > in the cell. Here is a sample of one of the formulas in a cell in the >> > column >> > above... >> > >> > =IF(AND(W16<>"",(COUNTA($W$3:W15)>0)(COUNTBLANK($W$3:W15)),(COUNTA($W17:W$22)=0)COUNTBLANK($W17:W$22)),$F16,"") >> > >> > "T. Valko" wrote: >> > >> >> The best solution to this depends on many conditions. Among those a >> >> >> >> What type of data is in the range, is it text or numeric, or can it be >> >> both? >> >> Are there any empty/blanks cells *within* the range? >> >> Are there any formulas in the range that might return formula blanks? >> >> >> >> As a starting point you can try something like this. Assumes no >> >> empty/blank >> >> cells within the range: >> >> >> >> =IF(COUNTA(A1:A10),INDEX(A1:A10,COUNTA(A1:A10)),"" ) >> >> >> >>  >> >> Biff >> >> Microsoft Excel MVP >> >> >> >> >> >> "Vicky" > wrote in message >> >> ... >> >> > Can someone give me a function to use so that the cell at the bottom >> >> > of >> >> > a >> >> > column equals the last nonblank cell above in that column? >> >> >> >> >> >> >> >> >> 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
If a cell equals _, at the next row that equals _, return value fr  CathyH  Excel Worksheet Functions  10  May 2nd 07 07:53 PM 
If cell is left blank, or equals zero, then cell equals a different cell  John McMurry  Excel Discussion (Misc queries)  3  April 13th 07 01:14 PM 
Very Basic Problem  Merged Cell Equals Contents of a Single Cell  jollynicechap  Excel Worksheet Functions  3  December 29th 06 09:16 PM 
Require a cell to be nonblank if another cell is nonblank  Herb Wexler  Excel Discussion (Misc queries)  1  February 1st 06 09:05 PM 
How to create/run "cell A equals Cell B put Cell C info in Cell D  abmb161  Excel Discussion (Misc queries)  5  January 26th 06 07:36 PM 