Newest month formula

[Jennifer]

I have a sheet with all 12 months and then I have a column next to the total
column that displays the data from the newest month.

Ex.

May Jun | | Total
1 1 1 2
1 2 2 3

The column that displays June's data again basically makes it easier for me
to look at and manipulate later.

Is there a way to automatically have that column update if I add data in a
July column?

 

[Sandy Mann]

Assuming that the months are in Columns B:M try:

=IF(COUNT(B2:M2)=0,"",LOOKUP(10^10,B2:M2))

to return the latest month's figure and copy down as required

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Huber57]

Jennifer,

In your 'latest' column, you can use and HLOOKUP formula to return the
latest month.

A B C D
Jan-08 Feb-08 Mar-08 Apr-08
1 2 2 4
2 2 2 1
3 5 3 5
etc

(The Jan-08 must be formatted as a date.)

Use this formula:
=HLOOKUP(MAX(A1:L1),A2:L20,1)

The first part of the formula MAX(A1:L1) will return the largest month
(since there are twelve months, A-L are the columns)

The second part is all of the data. So, if you have 20 rows of data, you
should type in A2:L20

The third part of the formula is how many cells down from the referenced
cell it looks. So, if it finds that Jun-08 is the largest date entered, it
will then look down one to return the first number. The second cell in your
'latest' column should look like this:
=HLOOKUP(MAX(A1:L1),A2:L20,2)

Make sense?

 

[recrit]

if the most recent month column (C) will always be next to the most
recent month (column B), could just use =OFFSET(C2,0,-1), then when
you insert a new month in between june and the most recent it will
adjust to the new one