How do I choose the last nonblank cell in a ROW?

D

DaChris

I have a row of sales data by month. I need to create a link in a seperate
worksheet to access the last data in the row. The link must differenate
between 0 as data and a blank cell as no data.
 
M

Mike H

Hi,

This returns the last populated cell in row 2 of sheet 1

=LOOKUP(2,1/(Sheet1!2:2>0),Sheet1!2:2)

Mike
 
L

Luke M

Something like this array* formula will work.

=OFFSET(A2,0,MIN(IF(ISBLANK(2:2),COLUMN(2:2)))-2)

*Confirm formula using Ctrl+Shift+Enter, not just Enter.
 
M

Mike H

Hi,

My previous post reyturns the lat populated cell text or numeric. For
numeric on try

=LOOKUP(6.022*10^23,Sheet1!2:2)

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error message "cannot shift nonblank cells off the worksheet" 2
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Looking uprange for next nonblank cell 4
Excel Error Help 1
how to return the value of the last nonblank cell in a row? 2
count how many rows contain nonblank in column A and blank in colu 5
Last NonBlank In Ranger 4
Adding only NONBLANK cells 2

Top