Can anyone suggest a formula that will permit a cell to display the last amount entered in a column?. I have a column that gets used daily, at the bottom of this column I would like it to display the last day / amount entered...... tough one (for me anyway) LOL
iMac
I can't tell if you are trying to auto populate the bottom cell, or if you want to have another cell show what the bottom cell contains.
For the latter, I use something like this...
=offset(a1,counta(a:a),0,1,1)
This assumes a header row, and you want to show the last item in column a.
Jason
Trying to autopopulate a cell , the last cell of a column.
OK, now you've got me confused.
Where is the data used to 'autopopulate' the last cell of the column to come from? And how does it get to that location?
Regards,
Barry
I appreciate you trying to help, let me try to explain better. I have a column that is 32 cells long. Each day I enter a figure. The first day of the month is cell #1, second day of month is cell #2 and so on. At any given time I want the last cell in column, #32, to display the last number entered in the column. So on the first day of month I want cell # 32 to show what is in cell # 1, on the second day of month I want cell # 32 to show the figure added in cell #2, ,on the third day I want cell # 32 to show what was entered into cell # 3. I want to autopopulate cell # 32 with the figure last entered into the column. :-)
I'm actually in column "I", figures are in I4 to I35, with the cell to be populated... I35.
Try this:
135: =OFFSET($I$13,COUNT(I14:I134),0)
Regards,
Barry
Jim,
Two questions, to help us find the best solution for you.
1. Will there be a possibility of a blank entry?
2. Are you entering the corresponding Date in another column?
Jerry
1 - there are blanks, for example today the column is blank after todays date, each day I add the days numbers, so today after the date "16th" it is blank, the 17th is blank, the 18th is blank and so on,
Presently the column starts at "I5" and runs down to "I35", with I36 being the cell I want to populate.
2 - no
Jim,
Here's an example that works in your case:
In the example, Date is in A and Data is in B. Column C contains a simple expression that allows us to easily determine the last entry and retrieve it. The Aux column can be hidden. I'm not using the Date for anything, it's good practice.
The expression in the Aux column is:
=IF(LEN(B)>0, ROW(),"")
The expression in the Footer Cell of the Data column is:
=LOOKUP(MAX(C), C,B)
Regards,
Jerry
jimfromdorch wrote:
1 - there are blanks, for example today the column is blank after todays date, each day I add the days numbers, so today after the date "16th" it is blank, the 17th is blank, the 18th is blank and so on,
Presently the column starts at "I5" and runs down to "I35", with I36 being the cell I want to populate.
2 - no
Oops read the norte including the actual cell addresses and responded in a rush before heading out the door for an appointment. That's one of the problems with cell addresses in column I 😕
Here's a corrected version, and some comments on the differences in the two approaches.
I35: =OFFSET($I$3,COUNT(I4:I34),0)
This approach works fine IF all of the cells from I4 to the one containing the last entry contain numeric data. Your answer above doesn't address this, noting only that the cells below the last entry are empty.
Jerry's approach, using an index column and checking for the number of characters displayed in the entry cells (using LEN() ) is more robust, especially for a column where the data is entered directly. Where the data is entered via a formula, the user needs to take care what is used to represent an 'empty' cell. Null strings are OK, as their length is zero. Anything else is going to create an index column entry. (not an issue in this case, though, as trhe data is being entered directly.
Regards,
Barry
formula to make a cell show last amount entered in a column?
