A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
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 ?



 
 
Thread Tools Display Modes
  #1  
Old June 8th 09, 03:59 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 390
Default 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?
Ads
  #2  
Old June 8th 09, 04:34 PM posted to microsoft.public.excel.worksheet.functions
Don Guillett
external usenet poster
 
Posts: 10,124
Default 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  
Old June 8th 09, 05:10 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_2_]
external usenet poster
 
Posts: 3,346
Default 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  
Old June 9th 09, 01:47 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 390
Default 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  
Old June 9th 09, 01:57 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 390
Default 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

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

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 09:40 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.