find last populated cell in range of cells
 

I have a worksheet that contains the actual and projected hours for
workers and I'm trying to figure out the function that would search
through the entire row and return the last cell of the row that
contains a non-blank value. Some of the cells contain hours (ex: 1.0,
15.0, 3.5) and other cells are blank. Some of the cells are
completely blank in between the specific range. For example the first
column contains the workers names and after that are the hours, the
very first row in the sheet is the week that the hours are projected
for... for this example, semi colons are used to represent a new cell:
A ; B ; C ; D ; E ;
F ; G ; H ; I ; J
1; Name ; 7-Aug; 14-Aug; 21-Aug; 28-Aug; 4-Sep; 11-Sep; 18-
Sep; 25-Sep; Total
2; John Smith; 1.0 ; 2.0 ; 2.5 ; ; ;
5.5 ; 5.2 ; ; 16.2
3; Jane Doe ; 2.0 ; 5.0 ;
1.5 ; ; ; ; ; ;
8.5

I want the function to search through the range and return the date
that is above the last unblank cell.
Range - B2:I2 returns 18-Sep (I don't want it to return 21Aug
because it is followed by a blank)
Range - B3:I3 returns 21-Aug

This function needs to be entirely data driven and the only thing that
can really be hard coded is the row that contains the date that needs
to be returned.

Thanks for all the help!

find last populated cell in range of cells
 

This should work for you...

=INDEX(A$1:I$1,1+MATCH(99999,B2:I2,1))

--
Rick (MVP - Excel)


"Maximus" wrote in message
...
I have a worksheet that contains the actual and projected hours for
workers and I'm trying to figure out the function that would search
through the entire row and return the last cell of the row that
contains a non-blank value. Some of the cells contain hours (ex: 1.0,
15.0, 3.5) and other cells are blank. Some of the cells are
completely blank in between the specific range. For example the first
column contains the workers names and after that are the hours, the
very first row in the sheet is the week that the hours are projected
for... for this example, semi colons are used to represent a new cell:
A ; B ; C ; D ; E ;
F ; G ; H ; I ; J
1; Name ; 7-Aug; 14-Aug; 21-Aug; 28-Aug; 4-Sep; 11-Sep; 18-
Sep; 25-Sep; Total
2; John Smith; 1.0 ; 2.0 ; 2.5 ; ; ;
5.5 ; 5.2 ; ; 16.2
3; Jane Doe ; 2.0 ; 5.0 ;
1.5 ; ; ; ; ; ;
8.5

I want the function to search through the range and return the date
that is above the last unblank cell.
Range - B2:I2 returns 18-Sep (I don't want it to return 21Aug
because it is followed by a blank)
Range - B3:I3 returns 21-Aug

This function needs to be entirely data driven and the only thing that
can really be hard coded is the row that contains the date that needs
to be returned.

Thanks for all the help!

find last populated cell in range of cells
 

Perhaps this version would be better in case nothing it filled in for the
employee (it shows an empty cell rather than an error))...

=IF(COUNT(B2:I2),INDEX(A$1:I$1,1+MATCH(99999,B2:I2 ,1)),"")

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
This should work for you...

=INDEX(A$1:I$1,1+MATCH(99999,B2:I2,1))

--
Rick (MVP - Excel)


"Maximus" wrote in message
...
I have a worksheet that contains the actual and projected hours for
workers and I'm trying to figure out the function that would search
through the entire row and return the last cell of the row that
contains a non-blank value. Some of the cells contain hours (ex: 1.0,
15.0, 3.5) and other cells are blank. Some of the cells are
completely blank in between the specific range. For example the first
column contains the workers names and after that are the hours, the
very first row in the sheet is the week that the hours are projected
for... for this example, semi colons are used to represent a new cell:
A ; B ; C ; D ; E ;
F ; G ; H ; I ; J
1; Name ; 7-Aug; 14-Aug; 21-Aug; 28-Aug; 4-Sep; 11-Sep; 18-
Sep; 25-Sep; Total
2; John Smith; 1.0 ; 2.0 ; 2.5 ; ; ;
5.5 ; 5.2 ; ; 16.2
3; Jane Doe ; 2.0 ; 5.0 ;
1.5 ; ; ; ; ; ;
8.5

I want the function to search through the range and return the date
that is above the last unblank cell.
Range - B2:I2 returns 18-Sep (I don't want it to return 21Aug
because it is followed by a blank)
Range - B3:I3 returns 21-Aug

This function needs to be entirely data driven and the only thing that
can really be hard coded is the row that contains the date that needs
to be returned.

Thanks for all the help!

find last populated cell in range of cells
 

Your example got a little skewed in the posting, but I believe this is what
you want.

=INDEX($1:$1,1,MAX(IF(NOT(ISBLANK(2:2)),COLUMN(2:2 ))))

Note that this is an array function, and needs to be confirmed using
Ctrl+Shift+Enter (hold down Ctrl+Shift, press Enter), not just Enter.

Formula is set to return value from row 1 that is above last nonblank cell
in row 2.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Maximus" wrote:

I have a worksheet that contains the actual and projected hours for
workers and I'm trying to figure out the function that would search
through the entire row and return the last cell of the row that
contains a non-blank value. Some of the cells contain hours (ex: 1.0,
15.0, 3.5) and other cells are blank. Some of the cells are
completely blank in between the specific range. For example the first
column contains the workers names and after that are the hours, the
very first row in the sheet is the week that the hours are projected
for... for this example, semi colons are used to represent a new cell:
A ; B ; C ; D ; E ;
F ; G ; H ; I ; J
1; Name ; 7-Aug; 14-Aug; 21-Aug; 28-Aug; 4-Sep; 11-Sep; 18-
Sep; 25-Sep; Total
2; John Smith; 1.0 ; 2.0 ; 2.5 ; ; ;
5.5 ; 5.2 ; ; 16.2
3; Jane Doe ; 2.0 ; 5.0 ;
1.5 ; ; ; ; ; ;
8.5

I want the function to search through the range and return the date
that is above the last unblank cell.
Range - B2:I2 returns 18-Sep (I don't want it to return 21Aug
because it is followed by a blank)
Range - B3:I3 returns 21-Aug

This function needs to be entirely data driven and the only thing that
can really be hard coded is the row that contains the date that needs
to be returned.

Thanks for all the help!

find last populated cell in range of cells
 

(Haven't had my coffee yet this morning<g)

Let's make that 2 characters shorter...

=IF(COUNT(B2:I2),INDEX(B$1:I$1,MATCH(99999,B2:I2,1 )),"")

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Perhaps this version would be better in case nothing it filled in for the
employee (it shows an empty cell rather than an error))...

=IF(COUNT(B2:I2),INDEX(A$1:I$1,1+MATCH(99999,B2:I2 ,1)),"")

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
This should work for you...

=INDEX(A$1:I$1,1+MATCH(99999,B2:I2,1))

--
Rick (MVP - Excel)


"Maximus" wrote in message
...
I have a worksheet that contains the actual and projected hours for
workers and I'm trying to figure out the function that would search
through the entire row and return the last cell of the row that
contains a non-blank value. Some of the cells contain hours (ex: 1.0,
15.0, 3.5) and other cells are blank. Some of the cells are
completely blank in between the specific range. For example the first
column contains the workers names and after that are the hours, the
very first row in the sheet is the week that the hours are projected
for... for this example, semi colons are used to represent a new cell:
A ; B ; C ; D ; E ;
F ; G ; H ; I ; J
1; Name ; 7-Aug; 14-Aug; 21-Aug; 28-Aug; 4-Sep; 11-Sep; 18-
Sep; 25-Sep; Total
2; John Smith; 1.0 ; 2.0 ; 2.5 ; ; ;
5.5 ; 5.2 ; ; 16.2
3; Jane Doe ; 2.0 ; 5.0 ;
1.5 ; ; ; ; ; ;
8.5

I want the function to search through the range and return the date
that is above the last unblank cell.
Range - B2:I2 returns 18-Sep (I don't want it to return 21Aug
because it is followed by a blank)
Range - B3:I3 returns 21-Aug

This function needs to be entirely data driven and the only thing that
can really be hard coded is the row that contains the date that needs
to be returned.

Thanks for all the help!

find last populated cell in range of cells
 

Hi,

You could use

=INDEX(B$1:I$1,MATCH(9^9,B2:I2))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Maximus" wrote:

I have a worksheet that contains the actual and projected hours for
workers and I'm trying to figure out the function that would search
through the entire row and return the last cell of the row that
contains a non-blank value. Some of the cells contain hours (ex: 1.0,
15.0, 3.5) and other cells are blank. Some of the cells are
completely blank in between the specific range. For example the first
column contains the workers names and after that are the hours, the
very first row in the sheet is the week that the hours are projected
for... for this example, semi colons are used to represent a new cell:
A ; B ; C ; D ; E ;
F ; G ; H ; I ; J
1; Name ; 7-Aug; 14-Aug; 21-Aug; 28-Aug; 4-Sep; 11-Sep; 18-
Sep; 25-Sep; Total
2; John Smith; 1.0 ; 2.0 ; 2.5 ; ; ;
5.5 ; 5.2 ; ; 16.2
3; Jane Doe ; 2.0 ; 5.0 ;
1.5 ; ; ; ; ; ;
8.5

I want the function to search through the range and return the date
that is above the last unblank cell.
Range - B2:I2 returns 18-Sep (I don't want it to return 21Aug
because it is followed by a blank)
Range - B3:I3 returns 21-Aug

This function needs to be entirely data driven and the only thing that
can really be hard coded is the row that contains the date that needs
to be returned.

Thanks for all the help!

find last populated cell in range of cells
 

Here's another one:

=IF(COUNT(B2:I2),LOOKUP(1E100,B2:I2,B1:I1),"")

Format as Date

--
Biff
Microsoft Excel MVP


"Maximus" wrote in message
...
I have a worksheet that contains the actual and projected hours for
workers and I'm trying to figure out the function that would search
through the entire row and return the last cell of the row that
contains a non-blank value. Some of the cells contain hours (ex: 1.0,
15.0, 3.5) and other cells are blank. Some of the cells are
completely blank in between the specific range. For example the first
column contains the workers names and after that are the hours, the
very first row in the sheet is the week that the hours are projected
for... for this example, semi colons are used to represent a new cell:
A ; B ; C ; D ; E ;
F ; G ; H ; I ; J
1; Name ; 7-Aug; 14-Aug; 21-Aug; 28-Aug; 4-Sep; 11-Sep; 18-
Sep; 25-Sep; Total
2; John Smith; 1.0 ; 2.0 ; 2.5 ; ; ;
5.5 ; 5.2 ; ; 16.2
3; Jane Doe ; 2.0 ; 5.0 ;
1.5 ; ; ; ; ; ;
8.5

I want the function to search through the range and return the date
that is above the last unblank cell.
Range - B2:I2 returns 18-Sep (I don't want it to return 21Aug
because it is followed by a blank)
Range - B3:I3 returns 21-Aug

This function needs to be entirely data driven and the only thing that
can really be hard coded is the row that contains the date that needs
to be returned.

Thanks for all the help!