Forum Discussion
How to bottom and second to bottom automatic send to overview?
I am using a spreadsheet to calculate my electricity consumption and a price every quarter.
Once a quarter I have to make a report. I would like it to generate the report automatic. We are sharing the electricity with someone else so there is a main measurement box and a secondary measurement box. Therefor the need for a report.
Sorry for the Danish languages in the sheet.
Colum 1 is date.
Colum 2 is where I put in information from main box.
Colum 3 calculates consumption on main box.
Colum 4 is where I put in information from secondary box.
Colum 5 calculates consumption from secondary box.
The rest of the column makes different calculations.
Colum 7 is where I put in the price from last quarter.
The box in the top is what I hope can become the report.
What I am looking for is a way for the report box to automatic take either input from the last filled cell or second last fill cell. I have tried to explain it in the picture.
Assuming your data is sorted, LARGE() should do the trick . You can change 1 to 2 if you want the second one from bottom.
=LARGE(your range, 1)
8 Replies
It sounds like you want to create an automated report in Excel that extracts data either from the last filled cell or the second-to-last filled cell in a specific column. You can achieve this by using Excel functions like INDEX, MATCH, and OFFSET. Here is how you can set it up:
Let us assume you want to extract data from Column 2 (where you put information from the main box).
- In your report cell (the one you want to automatically populate), enter the following formula to get the last filled cell's value:
=LOOKUP(2,1/(B:B<>""),B:B)
This formula looks for the last non-empty cell in Column B and returns its value.
- To extract data from the second-to-last filled cell in Column 2, use the following formula:
=LOOKUP(2,1/(B:B<>""),B:B)-INDEX(B:B,COUNTA(B:B)-1)
This formula calculates the difference between the last non-empty cell and the second-to-last non-empty cell in Column B, effectively giving you the value of the second-to-last filled cell.
- Adjust the formulas and column references as needed to match your specific column and data.
Now, your report cell will automatically display the value from the last filled cell and the second-to-last filled cell, allowing you to choose which one to use for your report.
Keep in mind that these formulas assume that your data is consistently entered in a chronological order, with no skipped rows, in the specified column (Column 2 in this example). If your data does not follow this pattern, you may need to modify the formulas accordingly. The text was created with the help of AI.
Hope this will help you.
Was the answer useful? Mark them as helpful! ...and like it.
This will help all forum participants.
Is there a mistake or am I not doing it correct?
I keep getting the same info box.
I tried to versions of the formula.
Assuming your data is sorted, LARGE() should do the trick . You can change 1 to 2 if you want the second one from bottom.
=LARGE(your range, 1)- It works great for the first two, as the reading up the total consumption only increases. However, the last one is the price and that varies up and down in value.
You can cover LARGE() with SORT() for the last column.
=SORT(LARGE(Your range,1))
