What is the easiest formula to use to use the the last cell with data in a column?

C12HD

I have data coming in from a form..... which creates a new row at the bottom of all the data in the sheet. I need a formula that will continuously pull form the last cell with data. Overall, I just need the last cell in the column to be divided by another to get a percentage, but I do not want to have to go in every month and update the cell in the formula. Example below shows that in row 8 the sales numbers was $28,000. Row two shows the goal was $30,000. Row 5 is where the formula is to say what percentage that was of the goal. Simple..... but I just need it to pull from the newest numbers in the "sales number" column.... I need this to be current so it needs to always be pulling the last amount that was submitted by the Form (which automatically goes to the bottom row).

KPH

Hi

If you add an auto number column to your sheet, every row that is added will be given a number. You can then use the MAX of that number to find the last row in your sheet using an INDEX MATCH formula. I prefer using auto number to using the date/time as date/time fails if two rows are submitted within the same minute.

To ensure you always capture the very last row in the sheet is it best to reference a full column ([Sales numbers]:[Sales numbers]) rather than referencing a specific range ([Sales numbers]5:[Sales numbers]50). This does mean you will need to offset your calculation column so it is not within the same column as your sales data (as this would create a circular reference).

Here is an example:

The formula in the yellow box is:

=INDEX([Sales numbers]:[Sales numbers], MATCH(MAX(Row:Row), Row:Row, 0))

You can set up an auto number column by selecting the auto number column type:

I hope this helps.

KPH

It looks like you have some blank cells that have row numbers in them. See row 24-27. They are all blank. Can you delete those rows? The auto number is added when new rows are added to the sheet via your form. So if you delete the ones that are causing a problem, when new ones are added, it should be OK.

KPH

Hi

If you add an auto number column to your sheet, every row that is added will be given a number. You can then use the MAX of that number to find the last row in your sheet using an INDEX MATCH formula. I prefer using auto number to using the date/time as date/time fails if two rows are submitted within the same minute.

To ensure you always capture the very last row in the sheet is it best to reference a full column ([Sales numbers]:[Sales numbers]) rather than referencing a specific range ([Sales numbers]5:[Sales numbers]50). This does mean you will need to offset your calculation column so it is not within the same column as your sales data (as this would create a circular reference).

Here is an example:

The formula in the yellow box is:

=INDEX([Sales numbers]:[Sales numbers], MATCH(MAX(Row:Row), Row:Row, 0))

You can set up an auto number column by selecting the auto number column type:

I hope this helps.

C12HD

Thank you so much for making that so straight forward and easy. I definitely think this should do exactly what I need, but for some reason the cell that I put the formula is blank. I copied and Pasted the formula below.... does it look I am missing something? The yellow cell is where the formula is. Does it have to do with my Auto Number Row starting with 16?

=INDEX([Sales Numbers]:[Sales Numbers], MATCH(MAX(Row:Row), Row:Row, 0))

KPH

It looks like you have some blank cells that have row numbers in them. See row 24-27. They are all blank. Can you delete those rows? The auto number is added when new rows are added to the sheet via your form. So if you delete the ones that are causing a problem, when new ones are added, it should be OK.

C12HD

Yay! That fixed it! Thank you so much!

KPH

Wonderful! Enjoy your new formula.