Forum Discussion
Linking to the last item in a column in the previous sheet
I am creating a new sheet of a spreadsheet. I want the first cell in a column populated by the last cell of the previous sheet. The previous sheet isn't finished yet, so I will have to search for that last row.
What should my new cell look like? I can manually change =2023!E231 to 2023!E232 when I add a row, but would like it to be automatic.
I am a 365 user and I use tables for manual input or imported data, so I know exactly where the final row lies at any stage of development. For the case of calculated data, I use dynamic arrays so again know where they finish.
In either case
= TAKE(priorData, -1)Conversely, if your data management strategy involves over-sized ranges with an unknown count of blank cells, TOCOL can be used to remove the blanks. In which case the formula is modified to read
= TAKE(TOCOL(priorData, 1),-1)- I'd like to play with that and understand it, even though Hans got me a solution that I like. Could you walk me through where to place that command, and what it will show? Since it doesn't show the 2023 page, I infer that this formula goes on that page. '2023'E:1 contains the word "balance", so I'm starting on row 2, if that is useful.
I have attached a file based upon John's Pivot Table sheet that shows the data carried/brought forward from 2021 to 2022. The formulas are all dynamic array formulas using defined names or structured references and are very different from traditional spreadsheet development.
Let me guess: we're coming near the end of the year 2023 and you're starting a new sheet with something like a ledger of financial transactions for 2024 and will want this first cell in column E of 2024! to be populated with the closing balance from that last row in 2023!
If that's accurate--or even just "close"--may I suggest an alternative? Keep the same database running.
Assuming you already have a date in, say, column A, and then various data elements in B-D (e.g., description, budget category, sub-category, certainly amount), you can just keep a running single database. If needed, go back and make all of the prior years part of a single database.
Excel is REALLY good at extracting any kind of summary (annualized or monthly or quarterly or other) you might desire from single running transactional databases. Using the PivotTable, it's almost automatic, can even show monthly expenses per year per budget category (assuming that's one of the data elements). I've attached a rudimentary example. The example only uses data for one year, but you can expand that to test my claim. I have a personal income expense tracker that uses a single transactional database to include income from various sources, expenses through checking accounts as well as credit card accounts. The pivot table is the only report needed to look at income and expenses per year per category.
- I suspect you are describing the best solution for my wants. Unfortunately, it appears to be way beyond my level of expertise.
HowardB1000 What does column E of the 2023 sheet contain?
If it contains numbers:
=LOOKUP(9.99999999999999E+307, '2023'!E:E)
If it contains text values:
=LOOKUP(REPT("z", 255), '2023'!E:E)
- It is a dollar amount: =IF(A232>0,E231-C232+D232," "), Where column A is a date, E is the balance, C is the expenditure and D is a deposit. I tried your first example, but it filled in zero, where ='2023'!E231 produced a number.
