Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find first previous cell with data
Hi, I am not "fluent" in Excel, so please make any answers "dummy" level.
I have a running balance column that spans multiple pages and I am trying to get the last balance from the previous page onto a cell in the following page. Additionally, there are column headings at the beginning of each page and I have all cells (except column heading cells) in this balance column conditionally formatted to not display duplicate balances, so there are cells that appear blank but technically are not. I read a post in General Questions dated 1/16/2006 by Derby Jim and answered by Bob Phillips that sounded like my situation. I tried both solutions that Bob presented and neither worked. I set up test worksheet as exampled in the post, used both formulas and both returned 0. Any help would be much appreciated. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find first previous cell with data
Here is a formula that may be more or less what you need =INDIRECT("A"&COUNTA(A1:A100)) This formula will cause the cell that contains it to display the contents of the last cell in column A that isnt blank. For the example, I only had it count from A1 to A100, but you may change that to whatever number you wish. This will only work for you if there are NO BLANK CELLS within the range being counted before the last non blank cell. Your actual formula may look more like this: =INDIRECT("A"&COUNTA(Sheet3!A1:A10)) due to the fact that you are traversing different worksheets. If you have any questions, gimme a shout. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find first previous cell with data
Mark,
Thank you for responding to my post. I tried the formula you suggested, it did not work. I got a NAME error. As my running balance column is I, I typed in the formula as follows: =INDIRECT("I"&COUNTI(A4:A10000)). I started with A4 as that is where my data starts-2 rows of column headers and a blank row before the first line of data. I typed in the formula in row 3 of page 3-2 rows of column headers precede the formula. As far I know I do not have any blanks. Hopefully you can shed some light. Thanks, Nancy "Mark" wrote: Here is a formula that may be more or less what you need =INDIRECT("A"&COUNTA(A1:A100)) This formula will cause the cell that contains it to display the contents of the last cell in column A that isnt blank. For the example, I only had it count from A1 to A100, but you may change that to whatever number you wish. This will only work for you if there are NO BLANK CELLS within the range being counted before the last non blank cell. Your actual formula may look more like this: =INDIRECT("A"&COUNTA(Sheet3!A1:A10)) due to the fact that you are traversing different worksheets. If you have any questions, gimme a shout. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find first previous cell with data
Your are close but just off =INDIRECT("I"&COUNTI(A4:A10000)) needs to be: =INDIRECT("I"&COUNTA(A4:A10000)). I can see why you did that but you are using the COUNTA function. Should work now... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to find the data of a next cell in a list | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
how to find last cell in column with data | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions |