Forum Home
Press F1
 
Thread ID: 40810 2003-12-19 03:39:00 Excel Tammy (5018) Press F1
Post ID Timestamp Content User
201632 2003-12-19 03:39:00 If I have several rows of formulae that pick up numbers or remain blank (as in "") how can I pick up the rightmost cell with a value in each row? I can find no function that will do that. Any suggestions? Tammy (5018)
201633 2003-12-19 05:25:00 Hi Tammy, if you have values with no blank columns in between one way would be to use the count function inside the address function to locate the address of the cell that isnt blank. If you have blank columns in between this wont give you the correct result, so you would need to deduct # blanks from the column value depending upon how many areas there are.

Find the last non-blank cell in row # 1 (no blanks)
=ADDRESS(1,COUNT(1:1))

Find the last non-blank cell in row # 7 (no blanks)
=ADDRESS(7,COUNT(7:7))

Find the last non-blank cell in row # 7 (with blanks - find last used cell in first area)
=ADDRESS(7,COUNT(7:7)-1)

hth 		parry (27)
201634 2003-12-19 05:32:00 oops the function should be COUNTA not COUNT. parry (27)
201635 2003-12-22 22:00:00 Thanks for your reply Parry.

The formula puts "" into some cells and this seems to throw the COUNTA function. The values across the sheet could be 15, 22, "", 65, "" in which case I would want to pick up the 65 being the last number but I do not want the last "".

I've also tried COUNTIF function using >0 as the criteria and that doesn't give the correct result either.

Cheers
TAmmy 		Tammy (5018)
201636 2003-12-22 23:30:00 Im not sure of the answer Tammy. I could create a custom function that could do it but there should be a combination of formulas that can do this. Try posting your question on www.mrexcel.com and ask for a formula solution.

regards
parry 		parry (27)
201637 2003-12-23 00:58:00 Hi Tammy, I have posted a question on MrExcel on your behalf as I would like to know the answer myself. If nobody can give an answer I will give you a custom function to do this but I prefer to use a formula where possible. parry (27)
201638 2003-12-23 01:29:00 Well that didnt take long. Theres some clever people out there. See the Mr Excel post (www.mrexcel.com) for the detail but the answer is either of these formulas where row 1 is the row your searching....

=ADDRESS(1,MATCH(9.99999999999999E+307,1:1))

or

=CELL("Address",INDEX(1:1,MATCH(9.99999999999999E+307,1:1))) 		parry (27)
201639 2003-12-23 03:11:00 Hi Parry

Thanks for that.. and I just solved it myself another way with an array formula as follows:

={OFFSET(A3,0,MAX(IF(ISNUMBER(A3:K3),COLUMN(A3:K3) ))-1)}

where the cell values in a3 to k3 were numbers, blanks or "" in the cells. In each case I tried the array selected the last number of the array.

Cheers
T 		Tammy (5018)
201640 2003-12-23 03:27:00 PS I never knew that Mr Excel.Com existed. Very interesting site.

I also did not mention that I wanted to pick up the value of the rightmost cell.

T 		Tammy (5018)
201641 2003-12-23 04:51:00 > I also did not mention that I wanted to pick up the
> value of the rightmost cell.

You could do that using Parry's examples by incorporating the "INDIRECT" function:

=INDIRECT(ADDRESS(1,MATCH(9.99999999999999E+307,1: 1)))

or

=INDIRECT(CELL("Address",INDEX(1:1,MATCH(9.99999999999999E+307,1:1))))


Mike. 		Mike (15)
1 2