# Determining Last Non-Blank Cell in Column?

#### The doomed

##### Active Member
I have a sheet set up as follows:

Cell I1 = Publication Date (Manually entered - always a Wednesday)

Col.A - Date - the Wednesday of each week for the foreseeable future
Col.F - Number of items
Col.G - Running total of the numbers entered in F
Col.H - Week number (incremental from 1)
Col.I - Average number to date

Col.I rows have the following formula (basically, if no figure in ColF show blank...
=IF(F7>=1,G7/H7,"")

Data entry starts on Row 3.

That all works fine in a nice simple way to give me what I want. However, I want to pull the latest average figure on to another sheet to present in a weekly report.

Can I use the publication date in I1 to determine what row to pull the average from? Or can I do something that will look to the last no-blank cell in Col.I?

Being honest - if there is something simple to look to the last non-blank cell in the colum that would be best going forward but I'm not fussed.
Any help greatly appreciated, as ever.

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### dave3009

##### Well-known Member
I just tested this and it seems to do the job

=INDEX(A:I, MATCH(I1,A:A, 0),9)

9 refers to column so this is a simple count from left to right

If it's last used row you need then

=INDEX(A:I, MATCH(9.9E+255,A:A),9)

assuming the column is numerical change the 9.9E+255 to "zzz" if it's text

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
Try:

=LOOKUP(9.99999999999999E+307,I:I)

#### The doomed

##### Active Member
Thanks to the both of you!

Rolled with the solutuion from Peter_SSs and works great.

Replies
3
Views
708
Replies
2
Views
129
Replies
0
Views
475
Replies
1
Views
178
Replies
19
Views
531

1,191,177
Messages
5,985,140
Members
439,942
Latest member
bkexcel11230

### 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.

### Which adblocker are you using?

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

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