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

Hi Flick558,

You said formula, so this may be of no use to you.

Otherwise copy paste in a standard module, enter the "searched for" number in F1 (can be changes to suit)
Run code and it looks on sheet 3 and returns to sheet 2 cell F2.

Regards,
Howard

Flick558 said:

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!

Click to expand...

This type of formula should return the column number of the last non-blank cell in a given row. In this case, row 4 is being referenced. I think this is what you are looking for...

=LOOKUP(1,1/(NOT(ISBLANK(4:4))),COLUMN(4:4))

If you are wanting to return the contents of the cell, use something like this:

=LOOKUP(1,1/(NOT(ISBLANK(4:4))),4:4)

I hope this helps!

You have a formula as you actually asked for, but in the event you use the code and have Excel 2010, change to this for LastColumn.


Howard

Thanks Everyone, perhaps I needed to provide some more information.

1) My excel knowledge is relatively limited so basic instructions would be helpful

2) I need the data in the last column for that specific row to insert into the cell on the 2nd sheet

EG:

Worksheet 1:

A / B / C / D / E

Drawing Number / Drawing Title / 1/1/2014 / 1/2/2014 / 1/3/2014

1 789 / ABC / 0 / / A

Worksheet 2:

Drawing Number / Drawing Title / Revision

1 789 / ABC / Need formula to return result from Worksheet 1 for same drawing, with the data from the last column (ie data from worksheet 1 E1)

Hope this helps!

Thanks,

Thanks MrMeinHerr, I do want to return the contents of the cell, however note that there are many columns that are blank inbetween... so I also need the formula to look past them and grab only the last one with data (before the formula) I was intending on doing this on a separate sheet so would need it to reference the data from another sheet, but can add to my existing sheet if that makes it easier?

Try rather something like:

=LOOKUP(9.99999999999999E+307,INDEX(Sheet2!A:E,MATCH(A2,Sheet2!A:A,0),0))

Aladin Akyurek said:

Try rather something like:

=LOOKUP(9.99999999999999E+307,INDEX(Sheet2!A:E,MATCH(A2,Sheet2!A:A,0),0))

Click to expand...

Thanks Aladin, I'm returning a #N/A result, could be that I'm not following the formula accurately

You have "MATCH(A2" though it's impossible for me to select this without it first referencing the sheet - is this correct?
Also when you note "Sheet2!A:E" does this mean all data on the sheet including headers? or literally just the first row
Same again for "(A2,sheet2!A:A,0)" is the A:A the entire column including headers?

Flick558 said:

Thanks Aladin, I'm returning a #N/A result, could be that I'm not following the formula accurately

You have "MATCH(A2" though it's impossible for me to select this without it first referencing the sheet - is this correct?
Also when you note "Sheet2!A:E" does this mean all data on the sheet including headers? or literally just the first row
Same again for "(A2,sheet2!A:A,0)" is the A:A the entire column including headers?

Click to expand...

A2 is supposed to be in a sheet other than Sheet2...

=LOOKUP(9.99999999999999E+307,INDEX(Sheet2!A:E,MATCH(A2,Sheet2!A:A,0),0))

This assumes A:E in Sheet2 as the area where to look for the last numeric value that pertains to A2. The row to seek the last numeric value is calculated/determined by the INDEX/MATCH bit.

And the formula goes into the same sheet as A2..., say in B2.

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)