I have a spreadsheet with multiple months across the top, rows for data entry
and a total row at the bottom. There may be months with no data, followed by
months with data. For example:

1/07 2/07 3/07 4/07 5/07 6/07
1 3 4
5 6 7
total 6 0 9 7 4 0

I need a formula or macro to find the last column with data and return the
month from the column header (i.e. "5/07")

Probably simple, but I can't figure it out. I'm using Excel 2003.
--
CG

try (assuming dates start in B1):

=INDEX($B$1:$G$1,0,MAX((B2:G2<"")*COLUMN($B$1:$G$ 1))-1)

Enter with Ctrl+Shift+Enter (*array formula*). You will get {} surrounding
the formula.

"CEG" wrote:

I have a spreadsheet with multiple months across the top, rows for data entry
and a total row at the bottom. There may be months with no data, followed by
months with data. For example:

1/07 2/07 3/07 4/07 5/07 6/07
1 3 4
5 6 7
total 6 0 9 7 4 0

I need a formula or macro to find the last column with data and return the
month from the column header (i.e. "5/07")

Probably simple, but I can't figure it out. I'm using Excel 2003.
--
CG

I had to tweak it a bit because I wasn't really starting in column A, but
once I changed the -1 at the end to -10, it works!

I don't completely understand it, but I'll figure it out later. Thanks a
bunch!
--
CG


"Toppers" wrote:

try (assuming dates start in B1):

=INDEX($B$1:$G$1,0,MAX((B2:G2<"")*COLUMN($B$1:$G$ 1))-1)

Enter with Ctrl+Shift+Enter (*array formula*). You will get {} surrounding
the formula.

"CEG" wrote:

I have a spreadsheet with multiple months across the top, rows for data entry
and a total row at the bottom. There may be months with no data, followed by
months with data. For example:

1/07 2/07 3/07 4/07 5/07 6/07
1 3 4
5 6 7
total 6 0 9 7 4 0

I need a formula or macro to find the last column with data and return the
month from the column header (i.e. "5/07")

Probably simple, but I can't figure it out. I'm using Excel 2003.
--
CG