Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 8th 09, 03:59 PM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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?

  #2   Report Post  
Old 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
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   Report Post  
Old 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
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   Report Post  
Old June 9th 09, 01:47 PM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old June 9th 09, 01:57 PM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 09:36 PM
FIND / SEARCH text compare cell to string in 3rd cell nastech Excel Discussion (Misc queries) 0 October 29th 07 03: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 03:48 PM


All times are GMT +1. The time now is 06:46 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017