Formula to return A1:B2 style range references?

[marklenel]

Before I embark upon some coding to create a new user-defined function, I was wondering if anyone knew of any Excel function that takes as it argument a worksheet range. The result of the function is nothing more than a string showing the A1:B2 style reference of the range.

I guess it could also work across multiple ranges; or indeed across multiple sheets / workbooks?

E.g.

=MYFUNCTION(A1:C5) returns a string "A1:C5" or even "Sheet1!A1:C5"
=MYFUNCTION(A1:C5,D7:E8) returns "A1:C5,D7:E8"

and so on...

Any help very gratefully received!

 

[xlbo]

Don't know if this'll work for you but

=ADDRESS(row_num,col_num)
will return the A1 style reference you desire - but it doesn't take the input you specified

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[marklenel]

Thanks for the suggestion... I've had a go with the ADDRESS function, but inputting the row_num etc. arguments goes against the simplicity I'm looking for...

The Range.Address property in VBA seems to be heading in the right direction...

 

[Loomah]

Hi
Just a quick starter for 10. However I can't get it to work with multiple ranges and don't have much time. As I say, a starter!

Function addrs(rng As Range) As String
addrs = rng.Address(False, False)
End Function

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Bowers74]

I created a function a while back, that returns the formula from a cell in A1 or R1C1 format that might help you out.

Function CellFormula(Source As Range, Optional RefType As Integer)
Application.Volatile
If Source.Count = 1 Then
    Select Case RefType
        Case Is > 2
            CellFormula = CVErr(xlErrNA)
        Case Is = 1
            CellFormula = Source.formula
        Case Is = 2
            CellFormula = Source.FormulaR1C1
        Case Else
            CellFormula = Source.formula
    End Select
Else
    CellFormula = CVErr(xlErrRef)
End If
End Function

I'm still not sure why you want to have a function to return a formula's reference. I would really like to know in which context you are planning on using this.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[marklenel]

Many thanks, I'll have a look at that. The context:-

I have a whole batch of data formatted as a set of tables on a single data worksheet. It is certain that the size & content of these tables will change over time. I would therefore like to create an 'contents menu' or index to these tables, containing (amongst other things) the range address (as A1-style).

I'm guessing that inputting this data using a worksheet function and selecting a the range of the data table will be easier and more flexible than manually typing the A1-style reference.

Ultimately this reference to a range in the data worksheet will be used to either populate a ComboBox (.ListFillRange property) or to create named references for use elsewhere in my code.

 

[Bowers74]

If your table is on sheet 1 and you want to have the table's cell references as text, you could use the following formula (modify it to fit your sheet and/or purpose):

="Sheet1!$A$1:"&ADDRESS(COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1),1)

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

marklenel,

I decided to combine my function with a modified version of Loomah's and came up with this:

Function FormulaRef(RefRange As Range, Optional RefType As Integer)
Application.Volatile
Select Case RefType
    Case Is > 2
        FormulaRef = CVErr(xlErrNA)
    Case Is = 1
        FormulaRef = RefRange.Address(0, 0, xlA1)
    Case Is = 2
        FormulaRef = RefRange.Address(0, 0, xlR1C1)
    Case Else
        FormulaRef = RefRange.Address(0, 0, xlA1)
End Select
End Function

As long as you have only one range of cells, this will do what you want (if you leave the second argument empty or enter 1). If you enter 2 for the second argument, it will return the R1C1 format of the reference. Anything else in the second argument (other than omitted, 1 or 2) returns the #N/A error.

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!