Formula to Show Full Address of Named Range without VBA

[Dsewardj]

This is a short and easy one and the answer is most likely no without VBA but...

I was wondering if there was a way I could show the full address of a named range. Let's say I have a named range in Sheet1 called FirstNames. The range address would be Sheet1!$A$2:$A$7.

<TABLE style="WIDTH: 76pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=100 x:str><COLGROUP><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 804" width=22><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 17pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17 width=22></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=78>A

​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=17 x:num>1

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>FirstNames</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=17 x:num>

2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Bob</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=17 x:num>

3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Tim</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=17 x:num>

4​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Sue</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=17 x:num>

5​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Bill</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=17 x:num>

6

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=17 x:num>7

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25></TD></TR></TBODY></TABLE>​

Now I know I can use the ROW and COLUMN functions to find the 1st row and 1st column of the named range.

=ROW(FirstNames)

would return 2 and...

=COLUMN(FirstNames)

would return 1

And from there I could use the ADDRESS function. But this only gives me the beginning of the range.

I can't use the COUNTA function because the last two rows do not contain any data, and I don't want to use a dynamic named range because it will not work for the mini-application I am building.

I was thinking there must be some way to find the end of the range considering the ROW and COLUMN functions can find the beginning of it.

Thanks for any help.

 

[Richard Schollar]

Hi

Maybe you could work with something like this - which would return the address range of a named range "data":

=ADDRESS(ROW(data),COLUMN(data))&":"&ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1)

If the range is a single cell range then it will return it like:

A1:A1

 

[Dsewardj]

Excellent! Thank you very much.

I feel so simple... I was already using the ROW and COLUMN functions, a simple extension into the ROWS and COLUMNS functions was all I needed.

Thank you again.

 

[excelroofing]

I am using this exact formula but trying to put it in a SUM function.

=sum(address(B1,COLUMN(gutters),4,true,"Ph")&":"&address(B1,column(gutters)+COLUMNS(gutters)-1,4,true,"Ph"))

Where B1 contains a row number, and "Ph" is the name of another sheet in the worksheet.

Using:

address(B1,COLUMN(gutters),4,true,"Ph")&":"&address(B1,column(gutters)+COLUMNS(gutters)-1,4,true,"Ph")

returns exactly the text I want, "Ph!BP15:Ph!BW15", so I figured I could put it within a SUM function and it would sum that range. But it actually returns the number 0. There are numbers in all those cells, so I am puzzled why it's returning 0. It does not give me an error. If I simply put =SUM(Ph!BP15:Ph!BW15) it returns the correct sum of 179. So why isn't it returning 179 when I derive it using the address functions separated by &":"& ?

I also tried adding INDIRECT( to each address function and this still returned 0.

Any help?

 

[shg]

Try

=SUM(INDIRECT(ADDRESS(B1, COLUMN(gutters), 4, TRUE, "Ph") & ":" & ADDRESS(B1, COLUMN(gutters) + COLUMNS(gutters) - 1, 4, TRUE)))

 

[excelroofing]

ErrorFunction INDIRECT parameter 1 value is 'Ph!BP15:Ph!BW15'. It is not a valid cell/range reference.

 

[shg]

The posted formula doesn't give that result. Try again.

 

[excelroofing]

The posted formula doesn't give that result. Try again.

Oops I forgot to mention I'm using Google Sheets.

I've made a sheet that mimics this problem.

Feel free to go make some attempts in the areas where it says FORMULA

https://docs.google.com/spreadsheets/d/1uoqhMMwyZLV1NB8Hw3VqSHZqnGoRo9BVQQ4CIxC8GIU/edit?usp=sharing

 

[shg]

I don't use Google Sheets, sorry.

 

[excelroofing]

=SUM(INDEX(Gutters,B1)) is the solution