If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 how to find the right most cell in a row with a value in it ?
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

how to find the right most cell in a row with a value in it ?

#1
June 8th 09, 03:59 PM posted to microsoft.public.excel.worksheet.functions
 Max external usenet poster 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
 Don Guillett external usenet poster 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
 Shane Devenshire[_2_] external usenet poster 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
 Max external usenet poster 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
 Max external usenet poster 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?

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Find & Replace: find part cell, replace whole cell katy Excel Worksheet Functions 2 June 19th 08 01:39 PM find last cell in range with data, display cell address sevi61 Excel Worksheet Functions 14 October 29th 07 08:36 PM FIND / SEARCH text compare cell to string in 3rd cell nastech Excel Discussion (Misc queries) 0 October 29th 07 02:51 AM Find First Non blank cell than find column header and return that value Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM Change the appearance cell where Find criteria is found in a cell Tomszar Excel Discussion (Misc queries) 3 December 30th 05 02:48 PM

All times are GMT +1. The time now is 11:39 AM.