SOLVED

# How to bottom and second to bottom automatic send to overview?

Copper Contributor

# 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.

8 Replies
best response confirmed by HenrikKruse (Copper Contributor)
Solution

# Re: How to bottom and second to bottom automatic send to overview?

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)``

# Re: How to bottom and second to bottom automatic send to overview?

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).

1. 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.

1. 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.

1. 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.

# Re: How to bottom and second to bottom automatic send to overview?

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.

# Re: How to bottom and second to bottom automatic send to overview?

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.

# Re: How to bottom and second to bottom automatic send to overview?

You can cover LARGE() with SORT() for the last column.
=SORT(LARGE(Your range,1))

# Re: How to bottom and second to bottom automatic send to overview?

It says it is not valid. Even if I just choose from G11 to G40 insted of the Table1.

If it does not work I can live with having to fill one cell manuel, just a bummer when the rest now works so well.

# Re: How to bottom and second to bottom automatic send to overview?

My bad. It should be like this. = LARGE(SORT(G11:G40),1)

# Re: How to bottom and second to bottom automatic send to overview?

Thank you for your help :) I still get the same error message, but it is okay.

1 best response

Accepted Solutions
best response confirmed by HenrikKruse (Copper Contributor)
Solution

# Re: How to bottom and second to bottom automatic send to overview?

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)``