Value of cell in row based on last non-empty cell in another column

trigger467

New Member
Joined
Sep 15, 2005
Messages
35
I am trying to figure out how to show the value of a cell in a row when another column in the same row has the last non-empty cell.

Example.

I have the days of the month listed in cells A2:A31 and sales figures in cells B2:B20.

On a summary page, I am trying to do a "Data accurate through ______" where the missing date is that from cell A20, since cell B20 is the last row with non-empty data in the column.

Any help is appreciated.

trigger467
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I have the days of the month listed in cells A2:A31 and sales figures in cells B2:B20.

On a summary page, I am trying to do a "Data accurate through ______" where the missing date is that from cell A20, since cell B20 is the last row with non-empty data in the column.
Use an "array formula." Copy and paste this formula into the editing box in Excel:

Code:
=INDEX(A2:A32,MATCH(TRUE,B2:B32="",0)-1,0)
Excel Workbook
ABCDE
1MonthSales
21400Data Accurate Through:7
32500
43600
54700
65800
76900
871000
98
109
1110
1211
1312
BUT
#VALUE!
Excel 2002
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top