Remember Me?

#1
June 8th 09, 03:59 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 390
how to find the right most cell in a row with a value in it ?

I have a need to have cell with a formula or series of formulas, that can
find the right most cell in a specific row that has a value in it.

I tried various flavors of lookup but was not able to make this work.

any ideas?

#2
June 8th 09, 04:34 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 10,124
how to find the right most cell in a row with a value in it ?

This finds the column num

=MATCH(LOOKUP(2,1/(14:14<""),14:14),14:14)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Max" wrote in message
...
I have a need to have cell with a formula or series of formulas, that can
find the right most cell in a specific row that has a value in it.

I tried various flavors of lookup but was not able to make this work.

any ideas?

#3
June 8th 09, 05:10 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2008 Posts: 3,346
how to find the right most cell in a row with a value in it ?

Depends on what you mean by "value"

=LOOKUP(9^9,3:3)

returns the last number in row 3

=LOOKUP(REPT("z",255),3:3)

returns the last text entery on row 3

=LOOKUP(2,1/(3:3<""),3:3)

returns the last non-blank cell.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Max" wrote:

I have a need to have cell with a formula or series of formulas, that can
find the right most cell in a specific row that has a value in it.

I tried various flavors of lookup but was not able to make this work.

any ideas?

#4
June 9th 09, 01:47 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 390
how to find the right most cell in a row with a value in it ?

sorry, yes I should have been more specific:

I have a row, formatted as numeric. Everytime a certian event happens that I
am tracking at work, I put a number in the next column to the right.

On another location in the worksheet, I need to have a forumula that will
look in the specific row and find the number, furthest to the right. They
are not in any kind of order that would allow a min, or max function to be
used.

I will try the lookup formula you provided.

Thanks !

"Shane Devenshire" wrote:

Depends on what you mean by "value"

=LOOKUP(9^9,3:3)

returns the last number in row 3

=LOOKUP(REPT("z",255),3:3)

returns the last text entery on row 3

=LOOKUP(2,1/(3:3<""),3:3)

returns the last non-blank cell.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Max" wrote:

I have a need to have cell with a formula or series of formulas, that can
find the right most cell in a specific row that has a value in it.

I tried various flavors of lookup but was not able to make this work.

any ideas?

#5
June 9th 09, 01:57 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 390
how to find the right most cell in a row with a value in it ?

The formula =lookup(9^9,3:3) works, with "3" being the row number.

questions:
1- what is the 9^9 ?
2- can i specify this for certian columns in a row? for example, look in row
B37:AH37 ?

"Shane Devenshire" wrote:

Depends on what you mean by "value"

=LOOKUP(9^9,3:3)

returns the last number in row 3

=LOOKUP(REPT("z",255),3:3)

returns the last text entery on row 3

=LOOKUP(2,1/(3:3<""),3:3)

returns the last non-blank cell.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Max" wrote:

I have a need to have cell with a formula or series of formulas, that can
find the right most cell in a specific row that has a value in it.

I tried various flavors of lookup but was not able to make this work.

any ideas?

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post katy Excel Worksheet Functions 2 June 19th 08 01:39 PM sevi61 Excel Worksheet Functions 14 October 29th 07 08:36 PM nastech Excel Discussion (Misc queries) 0 October 29th 07 02:51 AM Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM Tomszar Excel Discussion (Misc queries) 3 December 30th 05 02:48 PM

All times are GMT +1. The time now is 10:01 AM.