Address of Last used cell in Column "A" and Dynamic Named Range

[blbat]

Find the Address of Last used cell in Column "A" -using a formula only- whether numeric or text.

There will be NO blank cells between the beginning and end of the values in Column "A". The values may be numeric or text.

I know this:

=ADDRESS(MATCH(9.99999999999999E+307,A:A),1)
finds address of last numeric value, and that:

=ADDRESS(MATCH(REPT("z",255),A:A),1)
finds address of last text.

So I was trying to figure a way to find the addess of the last used cell in column "A" without regard to value type in the cell (numeric or text).

I thought about using a "IF(ISNUMBER( " statement such as:

=If(ISNUMBER(ADDRESS(MATCH(9.99999999999999E+307,A:A),1),ADDRESS(MATCH(9.99999999999999E+307,A:A),1),ADDRESS(MATCH(REPT("z",255),A:A),1)
but I get errors, I can't tell if my logic is flawed or my syntax is flawed.

My intent with the above statement is that If "ISNUMBER" is "TRUE" then give me the address, if "FALSE" then "ADDRESS(MATCH(REPT("z",255),A:A),1)" which would still give me the last address.

or is there a cleaner way?

 

[Jonmo1]

Re: Address of Last used cell in Column "A" -using a formula only- whether numeric or text

You can use this array formula entered with CTRL + SHIFT + ENTER

=ADDRESS(MATCH(2,1/(A1:A1000<>"")),1)

Note, in XL2003 or earlier, in array formulas you cannot use entire column refs like A:A.
You must use finite ranges like A1:A1000.
In XL2007 or higher, you CAN use A:A, but it's still not recommended.


Also, curious about why you want the Address?
Are you planning on using that address in a subsequent formula?
If so, what is that formula?
there is likely an easier way.

 

[VoG]

Re: Address of Last used cell in Column "A" -using a formula only- whether numeric or text

This should help you http://www.xldynamic.com/source/xld.LastValue.html#last_any

 

[Aladin Akyurek]

Re: Address of Last used cell in Column "A" -using a formula only- whether numeric or text

Find the Address of Last used cell in Column "A" -using a formula only- whether numeric or text.

There will be NO blank cells between the beginning and end of the values in Column "A". The values may be numeric or text.

I know this:

=ADDRESS(MATCH(9.99999999999999E+307,A:A),1)
finds address of last numeric value, and that:

=ADDRESS(MATCH(REPT("z",255),A:A),1)
finds address of last text.

So I was trying to figure a way to find the addess of the last used cell in column "A" without regard to value type in the cell (numeric or text).

I thought about using a "IF(ISNUMBER( " statement such as:

=If(ISNUMBER(ADDRESS(MATCH(9.99999999999999E+307,A:A),1),ADDRESS(MATCH(9.99999999999999E+307,A:A),1),ADDRESS(MATCH(REPT("z",255),A:A),1)
but I get errors, I can't tell if my logic is flawed or my syntax is flawed.

My intent with the above statement is that If "ISNUMBER" is "TRUE" then give me the address, if "FALSE" then "ADDRESS(MATCH(REPT("z",255),A:A),1)" which would still give me the last address.

or is there a cleaner way?

Try...

D1:

=MATCH(9.99999999999999E+307,A:A)

D2:

=MATCH(REPT("z",255),A:A)

=ADDRESS(IF(COUNT(D1:D2)=1,SUMIF(D1:D2,">0"),MAX(D1:D2)),1)

 

[T. Valko]

Re: Address of Last used cell in Column "A" -using a formula only- whether numeric or text

Find the Address of Last used cell in Column "A" -using a formula only- whether numeric or text.

There will be NO blank cells between the beginning and end of the values in Column "A". The values may be numeric or text.

I know this:

=ADDRESS(MATCH(9.99999999999999E+307,A:A),1)
finds address of last numeric value, and that:

=ADDRESS(MATCH(REPT("z",255),A:A),1)
finds address of last text.

So I was trying to figure a way to find the addess of the last used cell in column "A" without regard to value type in the cell (numeric or text).

I thought about using a "IF(ISNUMBER( " statement such as:

=If(ISNUMBER(ADDRESS(MATCH(9.99999999999999E+307,A:A),1),ADDRESS(MATCH(9.99999999999999E+307,A:A),1),ADDRESS(MATCH(REPT("z",255),A:A),1)
but I get errors, I can't tell if my logic is flawed or my syntax is flawed.

My intent with the above statement is that If "ISNUMBER" is "TRUE" then give me the address, if "FALSE" then "ADDRESS(MATCH(REPT("z",255),A:A),1)" which would still give me the last address.

or is there a cleaner way?

Try this array formula**:

=ADDRESS(MAX(IF(A1:A20<>"",ROW(A1:A20))),1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Richard Schollar]

Re: Address of Last used cell in Column "A" -using a formula only- whether numeric or text

Another:

=ADDRESS(LOOKUP(9.9E+307,CHOOSE({1,2,3},MATCH("zzzzzz",A:A),MATCH(9.9E+307,A:A),MAX(MATCH("zzzzzz",A:A),MATCH(9.9E+307,A:A)))),1)

 

[blbat]

Re: Address of Last used cell in Column "A" -using a formula only- whether numeric or text

jonmo1-
You are correct, the address was needed for a formula on an subsequent sheet.

In a workbook I have a sheet named "Combined" that gets data inputted manually at random intervals by different users.

on this sheet column "A" will always have a value input if there is any data at all entered on the Row. However, subsequent columns in the Row may have blanks.

The formulas on the subsequent sheet are:

=AVERAGEIFS(Combined!P2:P1000,Combined!O2:O1000,">30-Sep-11",Combined!O2:O1000,"<1-Jan-12",Combined!S2:S1000,"1")

{=MEDIAN(IF((Combined!O2:O1000 > DATE(2011,9,30))*(Combined!O2:O1000 < DATE(2012,1,1)),Combined!P2:P1000))} 'entered as an array

I was trying to get away from using the finite ranges that I have in these!

I saw your comment about NOT using A:A -btw, I am using Excel 2007- I assume that is because it would make the calcs slow?

VoG- THANKS for that link! I will be spending some time there, but I wonder why it has not been updated since 2004?

Aladin Akyurek- thanks

Richard Schollar- I've not seen "CHOOSE" before. I looked it up. I'm not sure I understand because I see you have 3 actions to select from in your index, but I see FOUR arguments after that comma after "CHOOSE({1,2,3}" I know it works...I don't understand WHY it works.

and btw, Thank you all very much. I have learned more at this board over the years than I have at any class.

 

[Jonmo1]

Re: Address of Last used cell in Column "A" -using a formula only- whether numeric or text

jonmo1-
You are correct, the address was needed for a formula on an subsequent sheet.

In a workbook I have a sheet named "Combined" that gets data inputted manually at random intervals by different users.

on this sheet column "A" will always have a value input if there is any data at all entered on the Row. However, subsequent columns in the Row may have blanks.

The formulas on the subsequent sheet are:

=AVERAGEIFS(Combined!P2:P1000,Combined!O2:O1000,">30-Sep-11",Combined!O2:O1000,"<1-Jan-12",Combined!S2:S1000,"1")

{=MEDIAN(IF((Combined!O2:O1000 > DATE(2011,9,30))*(Combined!O2:O1000 < DATE(2012,1,1)),Combined!P2:P1000))} 'entered as an array

I was trying to get away from using the finite ranges that I have in these!

I saw your comment about NOT using A:A -btw, I am using Excel 2007- I assume that is because it would make the calcs slow?

Yes, because of the slow calcs...

Now, what were you planning to DO with the address?
Substitute say P1000 with that ?


You might try Dynamic named ranges...

Try this..

On the formulas tab, click Name Manager
Click NEW
Name It LastRow
Scope - Sheet
Refers To : =MATCH(2,1/(Sheet1!$A:$A<>"")) adjust sheet name as needed
Click OK

Click NEW
Name it ColO (or whatever you want)
Scope - sheet
Refers To : =Sheet1!$O$2:INDEX(Sheet1!$O:$O,Sheet1!LastRow) adjust sheet names again
click OK

Repeat for ColP and ColS
click Close

Now you can use ColP and ColO and ColS in your formula...

=AVERAGEIFS(ColP,ColO,">30-Sep-11",ColO,"<1-Jan-12",ColS,"1")


Hope that helps.

 

[blbat]

Re: Address of Last used cell in Column "A" -using a formula only- whether numeric or text

jonmo1-

Yes, looking to substitute for the finite ranges.

Thanks for the Dynamic Named Ranges tip- yet ANOTHER thing in Excel I need to learn about!!!

I will go and see if I can make it work, I'll report back success or fail.


MUCH appreciation!

 

[blbat]

Re: Address of Last used cell in Column "A" -using a formula only- whether numeric or text

sorry to trouble you again jonmo1

I'm getting a #NAME? error.

Here's what I have for my Dynamic Ranges:

Name: LastRow
Scope: Combined 'this is sheetname
Refers to: =MATCH(2,1/(Combined!$A:$A<>""))

Name: ColO
Scope: Combined 'this is sheetname
Refers to: =Combined!$O$2:INDEX(Combined!$O:$O,Combined!LastRow)

Name: ColP
Scope: Combined 'this is sheetname
Refers to: =Combined!$P$2:INDEX(Combined!$P:$P,Combined!LastRow)

Name: ColS
Scope: Combined 'this is sheetname
Refers to: =Combined!$S$2:INDEX(Combined!$S:$S,Combined!LastRow)

these formulas are on the sheet named "Calc":
=AVERAGEIFS(Combined!P2:P297,Combined!O2:O297,">30-Sep-10",Combined!O2:O297,"<1-Jan-11",Combined!S2:S297,"1")

=AVERAGEIFS(ColP,ColO,">30-Sep-10",ColO,"<1-Jan-11",ColS,"1")

The first one works. The second one gives me the "#NAME? error.

I have tried changing the scope to- Scope: Calc (the sheet the formula is on) but receive the same error.

I know it's user error, but I can't figure out what it is!