Return header cell of last column with data
 

I have a spreadsheet with a cell linked to another spreadsheet "Last Date
Entered". The source spreadsheet has columns of numbers with a date header. I
need to formulate a cell that shows the date of the last column containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet in another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many rows? Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell within the
range that contains a numeric value? What type of data is in the range? Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet "Last Date
Entered". The source spreadsheet has columns of numbers with a date
header. I
need to formulate a cell that shows the date of the last column containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

It is another worksheet in the same workbook. The numbers are in 40 columns
(7 days of the week and then a total column for each wk) and are 270 rows in
length. Data in the range is all numbers in [h]:mm format, but the header is
in date format. Range C3:AP270 contains the data; line C2:AP2 contains the
date header.

Each column is totaled, so I was thinking of an If function. If the total
line C270:AP270 =0, then --- but I don't know how to choose the right most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet in another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many rows? Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell within the
range that contains a numeric value? What type of data is in the range? Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet "Last Date
Entered". The source spreadsheet has columns of numbers with a date
header. I
need to formulate a cell that shows the date of the last column containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

Try this...

=LOOKUP(1E100,Sheet1!C270:AP270,Sheet1!C2:AP2)

Format as Date

That will return the date from C2:AP2 that corresponds to the rightmost
numeric value in the range C270:AP270.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
It is another worksheet in the same workbook. The numbers are in 40
columns
(7 days of the week and then a total column for each wk) and are 270 rows
in
length. Data in the range is all numbers in [h]:mm format, but the header
is
in date format. Range C3:AP270 contains the data; line C2:AP2 contains the
date header.

Each column is totaled, so I was thinking of an If function. If the total
line C270:AP270 =0, then --- but I don't know how to choose the right
most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet in
another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many rows?
Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell within
the
range that contains a numeric value? What type of data is in the range?
Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet "Last
Date
Entered". The source spreadsheet has columns of numbers with a date
header. I
need to formulate a cell that shows the date of the last column
containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

this works fine, except now I see I have to use the addresses for the 7 days
and eliminate the Weekly total cell, as that is what is always returned
because there is alway a number in it even when only the first day of the
week has entries.(sum of the 7 days)

I need to look at C270:I270, K270:Q270, S270:Y270, AA270:AG270, AI270:AO270
then use C2:I2, K2:Q2, S2:Y2, AA2:AG2, AI2:AO2 for the dates to return

Is there a good site for syntax rules? I thought I could do this without
writing you again, but I'm getting an error message every time. Thanks again,

--
Excelsolutions4U


"T. Valko" wrote:

Try this...

=LOOKUP(1E100,Sheet1!C270:AP270,Sheet1!C2:AP2)

Format as Date

That will return the date from C2:AP2 that corresponds to the rightmost
numeric value in the range C270:AP270.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
It is another worksheet in the same workbook. The numbers are in 40
columns
(7 days of the week and then a total column for each wk) and are 270 rows
in
length. Data in the range is all numbers in [h]:mm format, but the header
is
in date format. Range C3:AP270 contains the data; line C2:AP2 contains the
date header.

Each column is totaled, so I was thinking of an If function. If the total
line C270:AP270 =0, then --- but I don't know how to choose the right
most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet in
another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many rows?
Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell within
the
range that contains a numeric value? What type of data is in the range?
Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet "Last
Date
Entered". The source spreadsheet has columns of numbers with a date
header. I
need to formulate a cell that shows the date of the last column
containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

Can you also explain what the 1E100 part of this formula is doing?
--
Excelsolutions4U


"T. Valko" wrote:

Try this...

=LOOKUP(1E100,Sheet1!C270:AP270,Sheet1!C2:AP2)

Format as Date

That will return the date from C2:AP2 that corresponds to the rightmost
numeric value in the range C270:AP270.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
It is another worksheet in the same workbook. The numbers are in 40
columns
(7 days of the week and then a total column for each wk) and are 270 rows
in
length. Data in the range is all numbers in [h]:mm format, but the header
is
in date format. Range C3:AP270 contains the data; line C2:AP2 contains the
date header.

Each column is totaled, so I was thinking of an If function. If the total
line C270:AP270 =0, then --- but I don't know how to choose the right
most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet in
another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many rows?
Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell within
the
range that contains a numeric value? What type of data is in the range?
Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet "Last
Date
Entered". The source spreadsheet has columns of numbers with a date
header. I
need to formulate a cell that shows the date of the last column
containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

I need to look at C270:I270, K270:Q270, S270:Y270,
AA270:AG270, AI270:AO270 then use C2:I2, K2:Q2,
S2:Y2, AA2:AG2, AI2:AO2 for the dates to return

Ok, that changes things considerably!

Try this array formula** :

=INDEX(C2:AO2,MAX((MOD(COLUMN(C270:AO270),8)<2)*( C270:AO270<"")*COLUMN(C270:AO270))-2)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
this works fine, except now I see I have to use the addresses for the 7
days
and eliminate the Weekly total cell, as that is what is always returned
because there is alway a number in it even when only the first day of the
week has entries.(sum of the 7 days)

I need to look at C270:I270, K270:Q270, S270:Y270, AA270:AG270,
AI270:AO270
then use C2:I2, K2:Q2, S2:Y2, AA2:AG2, AI2:AO2 for the dates to return

Is there a good site for syntax rules? I thought I could do this without
writing you again, but I'm getting an error message every time. Thanks
again,

--
Excelsolutions4U


"T. Valko" wrote:

Try this...

=LOOKUP(1E100,Sheet1!C270:AP270,Sheet1!C2:AP2)

Format as Date

That will return the date from C2:AP2 that corresponds to the rightmost
numeric value in the range C270:AP270.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
It is another worksheet in the same workbook. The numbers are in 40
columns
(7 days of the week and then a total column for each wk) and are 270
rows
in
length. Data in the range is all numbers in [h]:mm format, but the
header
is
in date format. Range C3:AP270 contains the data; line C2:AP2 contains
the
date header.

Each column is totaled, so I was thinking of an If function. If the
total
line C270:AP270 =0, then --- but I don't know how to choose the right
most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet in
another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many rows?
Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell within
the
range that contains a numeric value? What type of data is in the
range?
Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet "Last
Date
Entered". The source spreadsheet has columns of numbers with a date
header. I
need to formulate a cell that shows the date of the last column
containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

Read this entire thread (it's not very long!):

http://tinyurl.com/6yu3tp

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
Can you also explain what the 1E100 part of this formula is doing?
--
Excelsolutions4U


"T. Valko" wrote:

Try this...

=LOOKUP(1E100,Sheet1!C270:AP270,Sheet1!C2:AP2)

Format as Date

That will return the date from C2:AP2 that corresponds to the rightmost
numeric value in the range C270:AP270.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
It is another worksheet in the same workbook. The numbers are in 40
columns
(7 days of the week and then a total column for each wk) and are 270
rows
in
length. Data in the range is all numbers in [h]:mm format, but the
header
is
in date format. Range C3:AP270 contains the data; line C2:AP2 contains
the
date header.

Each column is totaled, so I was thinking of an If function. If the
total
line C270:AP270 =0, then --- but I don't know how to choose the right
most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet in
another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many rows?
Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell within
the
range that contains a numeric value? What type of data is in the
range?
Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet "Last
Date
Entered". The source spreadsheet has columns of numbers with a date
header. I
need to formulate a cell that shows the date of the last column
containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

I copied and pasted the formula, used procedure to enter an array, (saw the
squiggly brackets) but I get a #value error in the cell. I reformatted the
cell to Date and still have the same error message.

The cells analyzed are formatted as [h]:mm and currency and the result is a
date. The sheet is using 1904 date platform because some of the sheets have
negative hour values.
--
Excelsolutions4U


"T. Valko" wrote:

I need to look at C270:I270, K270:Q270, S270:Y270,
AA270:AG270, AI270:AO270 then use C2:I2, K2:Q2,
S2:Y2, AA2:AG2, AI2:AO2 for the dates to return

Ok, that changes things considerably!

Try this array formula** :

=INDEX(C2:AO2,MAX((MOD(COLUMN(C270:AO270),8)<2)*( C270:AO270<"")*COLUMN(C270:AO270))-2)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
this works fine, except now I see I have to use the addresses for the 7
days
and eliminate the Weekly total cell, as that is what is always returned
because there is alway a number in it even when only the first day of the
week has entries.(sum of the 7 days)

I need to look at C270:I270, K270:Q270, S270:Y270, AA270:AG270,
AI270:AO270
then use C2:I2, K2:Q2, S2:Y2, AA2:AG2, AI2:AO2 for the dates to return

Is there a good site for syntax rules? I thought I could do this without
writing you again, but I'm getting an error message every time. Thanks
again,

--
Excelsolutions4U


"T. Valko" wrote:

Try this...

=LOOKUP(1E100,Sheet1!C270:AP270,Sheet1!C2:AP2)

Format as Date

That will return the date from C2:AP2 that corresponds to the rightmost
numeric value in the range C270:AP270.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
It is another worksheet in the same workbook. The numbers are in 40
columns
(7 days of the week and then a total column for each wk) and are 270
rows
in
length. Data in the range is all numbers in [h]:mm format, but the
header
is
in date format. Range C3:AP270 contains the data; line C2:AP2 contains
the
date header.

Each column is totaled, so I was thinking of an If function. If the
total
line C270:AP270 =0, then --- but I don't know how to choose the right
most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet in
another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many rows?
Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell within
the
range that contains a numeric value? What type of data is in the
range?
Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet "Last
Date
Entered". The source spreadsheet has columns of numbers with a date
header. I
need to formulate a cell that shows the date of the last column
containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

Thanks for this information. I skimmed it but will read it entirely. Very
good explanation! I really appreciate all the help I get at this site.
--
Excelsolutions4U


"T. Valko" wrote:

Read this entire thread (it's not very long!):

http://tinyurl.com/6yu3tp

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
Can you also explain what the 1E100 part of this formula is doing?
--
Excelsolutions4U


"T. Valko" wrote:

Try this...

=LOOKUP(1E100,Sheet1!C270:AP270,Sheet1!C2:AP2)

Format as Date

That will return the date from C2:AP2 that corresponds to the rightmost
numeric value in the range C270:AP270.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
It is another worksheet in the same workbook. The numbers are in 40
columns
(7 days of the week and then a total column for each wk) and are 270
rows
in
length. Data in the range is all numbers in [h]:mm format, but the
header
is
in date format. Range C3:AP270 contains the data; line C2:AP2 contains
the
date header.

Each column is totaled, so I was thinking of an If function. If the
total
line C270:AP270 =0, then --- but I don't know how to choose the right
most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet in
another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many rows?
Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell within
the
range that contains a numeric value? What type of data is in the
range?
Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet "Last
Date
Entered". The source spreadsheet has columns of numbers with a date
header. I
need to formulate a cell that shows the date of the last column
containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

I'll put together a small sample file later on. I'll be away for a few
hours.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I copied and pasted the formula, used procedure to enter an array, (saw the
squiggly brackets) but I get a #value error in the cell. I reformatted the
cell to Date and still have the same error message.

The cells analyzed are formatted as [h]:mm and currency and the result is
a
date. The sheet is using 1904 date platform because some of the sheets
have
negative hour values.
--
Excelsolutions4U


"T. Valko" wrote:

I need to look at C270:I270, K270:Q270, S270:Y270,
AA270:AG270, AI270:AO270 then use C2:I2, K2:Q2,
S2:Y2, AA2:AG2, AI2:AO2 for the dates to return

Ok, that changes things considerably!

Try this array formula** :

=INDEX(C2:AO2,MAX((MOD(COLUMN(C270:AO270),8)<2)*( C270:AO270<"")*COLUMN(C270:AO270))-2)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
this works fine, except now I see I have to use the addresses for the 7
days
and eliminate the Weekly total cell, as that is what is always returned
because there is alway a number in it even when only the first day of
the
week has entries.(sum of the 7 days)

I need to look at C270:I270, K270:Q270, S270:Y270, AA270:AG270,
AI270:AO270
then use C2:I2, K2:Q2, S2:Y2, AA2:AG2, AI2:AO2 for the dates to return

Is there a good site for syntax rules? I thought I could do this
without
writing you again, but I'm getting an error message every time. Thanks
again,

--
Excelsolutions4U


"T. Valko" wrote:

Try this...

=LOOKUP(1E100,Sheet1!C270:AP270,Sheet1!C2:AP2)

Format as Date

That will return the date from C2:AP2 that corresponds to the
rightmost
numeric value in the range C270:AP270.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
It is another worksheet in the same workbook. The numbers are in 40
columns
(7 days of the week and then a total column for each wk) and are 270
rows
in
length. Data in the range is all numbers in [h]:mm format, but the
header
is
in date format. Range C3:AP270 contains the data; line C2:AP2
contains
the
date header.

Each column is totaled, so I was thinking of an If function. If the
total
line C270:AP270 =0, then --- but I don't know how to choose the
right
most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet in
another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many
rows?
Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell
within
the
range that contains a numeric value? What type of data is in the
range?
Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet
"Last
Date
Entered". The source spreadsheet has columns of numbers with a
date
header. I
need to formulate a cell that shows the date of the last column
containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

P.S.

Are there *any* TEXT entries in C270:AO270?

If there's numbers only the formula should work.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I'll put together a small sample file later on. I'll be away for a few
hours.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I copied and pasted the formula, used procedure to enter an array, (saw
the
squiggly brackets) but I get a #value error in the cell. I reformatted
the
cell to Date and still have the same error message.

The cells analyzed are formatted as [h]:mm and currency and the result is
a
date. The sheet is using 1904 date platform because some of the sheets
have
negative hour values.
--
Excelsolutions4U


"T. Valko" wrote:

I need to look at C270:I270, K270:Q270, S270:Y270,
AA270:AG270, AI270:AO270 then use C2:I2, K2:Q2,
S2:Y2, AA2:AG2, AI2:AO2 for the dates to return

Ok, that changes things considerably!

Try this array formula** :

=INDEX(C2:AO2,MAX((MOD(COLUMN(C270:AO270),8)<2)*( C270:AO270<"")*COLUMN(C270:AO270))-2)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
this works fine, except now I see I have to use the addresses for the
7
days
and eliminate the Weekly total cell, as that is what is always
returned
because there is alway a number in it even when only the first day of
the
week has entries.(sum of the 7 days)

I need to look at C270:I270, K270:Q270, S270:Y270, AA270:AG270,
AI270:AO270
then use C2:I2, K2:Q2, S2:Y2, AA2:AG2, AI2:AO2 for the dates to return

Is there a good site for syntax rules? I thought I could do this
without
writing you again, but I'm getting an error message every time. Thanks
again,

--
Excelsolutions4U


"T. Valko" wrote:

Try this...

=LOOKUP(1E100,Sheet1!C270:AP270,Sheet1!C2:AP2)

Format as Date

That will return the date from C2:AP2 that corresponds to the
rightmost
numeric value in the range C270:AP270.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
It is another worksheet in the same workbook. The numbers are in 40
columns
(7 days of the week and then a total column for each wk) and are
270
rows
in
length. Data in the range is all numbers in [h]:mm format, but the
header
is
in date format. Range C3:AP270 contains the data; line C2:AP2
contains
the
date header.

Each column is totaled, so I was thinking of an If function. If the
total
line C270:AP270 =0, then --- but I don't know how to choose the
right
most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet
in
another
file?

The source spreadsheet has columns of numbers with a date header.

Are these numbers all in the same row or are the numbers in many
rows?
Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell
within
the
range that contains a numeric value? What type of data is in the
range?
Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet
"Last
Date
Entered". The source spreadsheet has columns of numbers with a
date
header. I
need to formulate a cell that shows the date of the last column
containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U

Return header cell of last column with data
 

Here's a small sample file that demonstrates this:

xLookupLast.xls 14kb

http://cjoint.com/?jefblGRaby

I've used samller ranges so that everything fits on the screen without
having to scroll.

Start adding or removing numbers from row 4 and you'll see that this works.
The "yellow" cells are ignored.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
P.S.

Are there *any* TEXT entries in C270:AO270?

If there's numbers only the formula should work.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I'll put together a small sample file later on. I'll be away for a few
hours.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I copied and pasted the formula, used procedure to enter an array, (saw
the
squiggly brackets) but I get a #value error in the cell. I reformatted
the
cell to Date and still have the same error message.

The cells analyzed are formatted as [h]:mm and currency and the result
is a
date. The sheet is using 1904 date platform because some of the sheets
have
negative hour values.
--
Excelsolutions4U


"T. Valko" wrote:

I need to look at C270:I270, K270:Q270, S270:Y270,
AA270:AG270, AI270:AO270 then use C2:I2, K2:Q2,
S2:Y2, AA2:AG2, AI2:AO2 for the dates to return

Ok, that changes things considerably!

Try this array formula** :

=INDEX(C2:AO2,MAX((MOD(COLUMN(C270:AO270),8)<2)*( C270:AO270<"")*COLUMN(C270:AO270))-2)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
this works fine, except now I see I have to use the addresses for the
7
days
and eliminate the Weekly total cell, as that is what is always
returned
because there is alway a number in it even when only the first day of
the
week has entries.(sum of the 7 days)

I need to look at C270:I270, K270:Q270, S270:Y270, AA270:AG270,
AI270:AO270
then use C2:I2, K2:Q2, S2:Y2, AA2:AG2, AI2:AO2 for the dates to
return

Is there a good site for syntax rules? I thought I could do this
without
writing you again, but I'm getting an error message every time.
Thanks
again,

--
Excelsolutions4U


"T. Valko" wrote:

Try this...

=LOOKUP(1E100,Sheet1!C270:AP270,Sheet1!C2:AP2)

Format as Date

That will return the date from C2:AP2 that corresponds to the
rightmost
numeric value in the range C270:AP270.

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
It is another worksheet in the same workbook. The numbers are in
40
columns
(7 days of the week and then a total column for each wk) and are
270
rows
in
length. Data in the range is all numbers in [h]:mm format, but the
header
is
in date format. Range C3:AP270 contains the data; line C2:AP2
contains
the
date header.

Each column is totaled, so I was thinking of an If function. If
the
total
line C270:AP270 =0, then --- but I don't know how to choose the
right
most
column with 0). Thanks a bunch for your help.
Excelsolutions4U


"T. Valko" wrote:

Need more info:

another spreadsheet

Does that mean another worksheet in the same file or a worksheet
in
another
file?

The source spreadsheet has columns of numbers with a date
header.

Are these numbers all in the same row or are the numbers in many
rows?
Where
are these numbers? For example - A2:F10 or A2:Z2.

I need...the date of the last column containing data.

You need the date header that corresponds to the rightmost cell
within
the
range that contains a numeric value? What type of data is in the
range?
Is
it all numeric? Any text? Any formula blanks?

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a spreadsheet with a cell linked to another spreadsheet
"Last
Date
Entered". The source spreadsheet has columns of numbers with a
date
header. I
need to formulate a cell that shows the date of the last column
containing
data. Thanks! (Without this resource I couldn't do my job)--
Excelsolutions4U