Display last value from a row or column?

rkaufmann87 wrote:
I'm using Numbers 09 and want to display the last value entered in a column in another table.

For example in Table A I have columns set up by date with simple sums at the bottom of each column. I add new sums every day depending on the values from that day. What I'd like to do is get the last daily total and enter that value into another table called Table B. Is there a formula that will do that?

If I'm not being clear enough please let me know what additional information you need and I will be happy to provide it.

Hi rk,

I'm not getting a clear picture.

My impression is that you have a separate column for each date, summed in the bottom row of that column, and that you want to transfer each of those sums to a corresponding cell to another table.

If so, this may do the job:


Table 1 has one Header row, containing the dates, and one Footer row containing the sums. Table 2 (cell A2) recalculates the sums using the formula shown in the image. Note that Numbers does not include Header or Footer cells in this calculation. note that the formula produces a blank (actually the empty string ( "" ) where nothing has been entered into the first data cell in a column (eg. May 5 and May 7).

Row 1 of Table 2 contains the formula
=Table 1::A1

Both formulas are filled right into other columns on Table 2.

Regards,
Barry

rkaufmann87 wrote:
Hi Barry,

Thanks for posting the example, not quite though. In your sample Table A is transferring all the totals to Table B. What I'd like is as I enter the data in the columns in Table A Table B then picks up the latest update in a single cell. For example lets say Table A's Column A is May 1 and the total is 45, let's say that sum is placed in A15. Table B automatically picks up A15 from Table A and makes a duplicate in Table B cell A1, then the next day Table A's Column B is May 2 and the total is 90 (cell B15), then Table B senses the latest total is 90 and enters that in cell A1 again. Is this possible?

Here's another go.



Table 1 has a second Header row added (row 2) Cells in this row contain the formula
=IF(A1=MAX($1:1),COLUMN(),"")

Which returns the column number of the cell in row 1 containing the latest date. (4) This number is used by Table 2 to determine the column from which to return the total in the bottom (footer) row. (see below)

A1 in Table 2 and Table 3 contain the same formula:
=MAX(Table 1 :: $1:1)

This returns the latest date from row 1 of Table 1.

A2 in Table 2 and Table 3 contain formulas that return the value in the bottom cell of the column containing the latest date in row 1.

Table 2: =OFFSET(Table 1 :: $A$1,ROWS('May 1, 2010')-1,MAX(Table 1 :: $2:2)-1)
Table 3: =OFFSET(Table 1 :: $A$1,ROWS('May 1, 2010')-1,COUNT(Table 1 :: $1:1)-1)

Both use the same base ($A$1) and the same row offset (ROWS('May 1, 2010')-1) to reach the bottom row of Table 1.

Table 2 uses the maximum (and only) numerical value in row 2 of Table 1 ( MAX(Table 1 :: $2:2) ), then subtracts 1 to reach the fourth column of table 1.

Table 3 uses the same means to determine the row offset, but counts the number of dates entered into row 1 of Table 1 ( COUNT(Table 1 :: $1:1) ), then subtracts 1 to reach the same cell.

I prefer the method in Table 3 because it avoids the need for the second Header row and the possibility of overwriting the formulas in that row. (Row 2 of Table 1 may be deleted without affecting Table 3.) It does require that there be no empty cells in Row 1 from Column A to the column containing the latest date.

Regards,
Barry

Here's a thread in Feedback about Discussions with the information you need for including pictures in posts.

rkaufmann87 wrote:
This surely can't be that difficult the url for the photo I uploaded to Flicr is:

http://www.flickr.com/photos/rkaufmann87/4655109182/

but I keep getting...

When you wanted this:


Looks like Flickr is supplying you with a bad address. I went to the URL you provided above, then did a right click/control-click on the image and chose Copy Link location.

That gave me this: /___sbsstatic___/migration-images/115/11594180-1.jpg
Which I pasted in place of the one above, with the results you see.

On your example, Table 2::F2 currently shows the total for Table 1:: (column) D, taken from Table 1::D11.

Your description says you want Table 2::F2 to update to the total shown in Table 1:E11 when "Table 1 is updated here" ("here" referring to the column E total shown in E11).

That pretty much agrees with the assumptions I made in my second post.

Either of the formulas in A2 of my Table 2 or my Table 3 will do the job. As i said in the previous post, my preferences is for the one in Table 3::A2, as it does not require the addition of Row 2 to my Table 1.

The formula

=OFFSET(Table 1 :: $A$1,ROWS('May 1, 2010')-1,COUNT(Table 1 :: $1:1)-1)

is fully portable, and may be placed into your Table 2::F2 (or any other cell in your Table 2) without change.

You had not specified the number of rows on your Table 1, so I wrote the formula to work with any number of rows, provided you place the Date in Row 1 and the daily SUM in the last row of Table 1.

The update to the new sum is triggered when the new date is entered in the next column.

Regards,
Barry

rkaufmann87 wrote:
OK, I copied and pasted =OFFSET(Table 1 :: $A$1,ROWS('May 1, 2010')-1,COUNT(Table 1 :: $1:1)-1) and placed it into Table 2 F2 and get an error that says May 1, 2010 isn't a valid reference.

Select 'May 1, 2010' (including the single quotes) in the formula, delete it, hover the mouse over Table 1 to make the column reference tabs appear, then click on any one of the tabs. The function ROWS() counts the number of rows in the referenced column, so any column in the table will do. I used Column A, and Numbers substituted the column name from the Header cell in Row 1.

Barry