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. |
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. |
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. |
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... |
All times are GMT +1. The time now is 01:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com