find and return last cell in column with data (ignoring those with no data inbetween)

[Flick558]

Hi,
I need to write a conditional formula that will look up in a 2nd worksheet the last coloumn with data (note that there will be some coloumns in the row that will have data and some that will be blank, I need the last column for that specific row to be the return result)

Thanks!

 

[Aladin Akyurek]

OK Now I'm getting a result of #REF!

I'm happy to remove the 2nd sheet all together and I can instead just use 1 sheet for the formula (hopefully this will simplify for me..)

so new formula would be in for example Cell P2 needing to find the column prior with data starting from C2:O2 (some cells in this range will be blank)

Are we returning a text value or a number?

 

[MrMeinHerr]

If your data is on Sheet1, for example, then you can use something like the following on another worksheet to return the last entry on row 2:

=LOOKUP(1,1/(NOT(ISBLANK(Sheet1!2:2))),Sheet1!2:2)

 

[Flick558]

Could be either or Text or number value

 

[Flick558]

If your data is on Sheet1, for example, then you can use something like the following on another worksheet to return the last entry on row 2:

=LOOKUP(1,1/(NOT(ISBLANK(Sheet1!2:2))),Sheet1!2:2)

Thanks MrMeinHerr think that one is exactly what I need!!!
Much appreciated!

 

[Aladin Akyurek]

Could be either or Text or number value

The following will return the last value that is not a blank corresponding to a drawing number:

=LOOKUP(9.99999999999999E+307,
  1/(1-(INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0)="")),
  INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0))

 

[Flick558]

Hi Everyone, I was confident that the forumla =lookup(1,1/not(isblank(sheet1!2:2))),sheet1!2:2) was working, then I came across some lines where for some reason the formula isn't working properly for example row 7 has value C in column Q, is blank in column R and has value D in column S, yet the formula returns result of C.. will only update to D if I insert a value into column R? Note that prior to column Q there is a combination of blank cells and cells with a value?

 

[Aladin Akyurek]

Hi Everyone, I was confident that the forumla =lookup(1,1/not(isblank(sheet1!2:2))),sheet1!2:2) was working, then I came across some lines where for some reason the formula isn't working properly for example row 7 has value C in column Q, is blank in column R and has value D in column S, yet the formula returns result of C.. will only update to D if I insert a value into column R? Note that prior to column Q there is a combination of blank cells and cells with a value?

Did you try post #15 ?

 

[Flick558]

Did you try post #15 ?

I'm not familiar with "code" do I need to do this differently to entering a formula?

 

[Aladin Akyurek]

I'm not familiar with "code" do I need to do this differently to entering a formula?

It's not code, just a formula.

Sheet1

Drawing Number	Drawing Title	1/1/2014	1/2/2014	1/3/2014
1 789	ABC	0		A
3 84	JAD	4	V
55 3	VAD	0		66

<COLGROUP> <COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6343" width=178> <COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4778" width=134> <COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2673" width=75> <COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3185" width=90> <COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2730" width=77> <TBODY>
</TBODY>

Sheet2

Drawing Number	Drawing Title	Revision
1 789	ABC	A
3 84	JAD	V
55 3	VAD	66

<TBODY>
</TBODY>

In C2 of Sheet2 just enter and copy down:

=LOOKUP(9.99999999999999E+307,1/(1-(INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0)="")),INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0))

 

[Flick558]

It's not code, just a formula.

Sheet1

Drawing Number	Drawing Title	1/1/2014	1/2/2014	1/3/2014
1 789	ABC	0		A
3 84	JAD	4	V
55 3	VAD	0		66

<COLGROUP> <COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6343" width=178> <COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4778" width=134> <COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2673" width=75> <COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3185" width=90> <COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2730" width=77> <TBODY>
</TBODY>

Sheet2

Drawing Number	Drawing Title	Revision
1 789	ABC	A
3 84	JAD	V
55 3	VAD	66

<TBODY>
</TBODY>

In C2 of Sheet2 just enter and copy down:

=LOOKUP(9.99999999999999E+307,1/(1-(INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0)="")),INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0))

Thanks! that seems to work, is there a way if all cells are blank that the formula can return the result "no record of issue"