5

I'm trying to get an absolute range address from single input. Something like this.

Formula
=SOME.MAGIC(A1:B10)

Output (string)
"$A$1:$B$10"

Attempt 1

I'm able to get this result but using significantly complex approach with following (array) formula.

{=ADDRESS(MIN(ROW(A1:B10)),MIN(COLUMN(A1:B10)),1,1)&":"&ADDRESS(MAX(ROW(A1:B10)),MAX(COLUMN(A1:B10)),1,1)}

Where (as you can see) is string A1:B10 used four time.

Attempt 2

My other attempt was using function FORMULATEXT. The link for a range is there only once, but it's nasty workaround using one more cell and works relatively good only for relative addresses.It doesn't work for named ranges.

C1=$A$1:$B$10
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1) //Returns string "$A$1:$B$10" ✓

But
C1=A1:B10
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1) //Returns string "A1:B10" ✘

And
C1=Named_range
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1) //Returns string "Named_range" ✘

Attempt 3

Next step is combination of attempt 1+2. It works, but I'm not happy with it.

C1=A1:B10
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1)
C3{=ADDRESS(MIN(ROW(INDIRECT(C2))),MIN(COLUMN(INDIRECT(C2))),1,1)&":"&ADDRESS(MAX(ROW(INDIRECT(C2))),MAX(COLUMN(INDIRECT(C2))),1,1)}

Is there easier way (less complex formula) with same output?

2
  • Can you explain why you want to do this? It seems strange to want to convert relative refs to absolute refs as text. If you are going to use the text with indirect, you don't actually need the $ sign, as the text reference will remain as is (absolute) even when you move or copy the cell containing the text. A text reference is absolute by definition. – Mobus Jul 21 at 13:08
  • Hard to explain. Basically I'm trying to bend Excel worksheet to automate some tasks which are not meant to be done in table processor (normally I would use something else, but Excel is only tool we can use here). There is nasty hacky formula using this address many times and formula length limit around 8000 characters... – Lluser Jul 22 at 13:03
3

This would be another way:

=CELL("address",A1:B10)&":"&CELL("address",INDEX(A1:B10,ROWS(A1:B10),COLUMNS(A1:B10)))

And if you have Office 365 use LET to reference the range only once

=LET(ref, A1:B10, CELL("address",ref)&":"&CELL("address",INDEX(ref,ROWS(ref),COLUMNS(ref)))
1
  • Thanks. CELL function is cool improvement. Unfortunately, it's really limited by Excel 2013 capabilities, so I can't use none of great new features of recent Excel versions. – Lluser Jul 22 at 13:11
5

Consider the user defined function:

Option Explicit

Public Function adres(rng As Range) As String
    adres = rng.Address(True, True)
End Function

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from an Excel cell:

=adres(A1:B10)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

1
  • Thanks for reply. I like your solution. But macros are prohibited in my case. – Lluser Jul 22 at 12:54
2

Actually, your attempt #1 is exactly the way to do it. If you are looking for a way to make it appear more like a function (less repeats of the reference/ single use of the "argument" address), the best way is using LAMBDA in Office 365. With LAMDA, you can provide arguments to a named range, defining MAGIC as

=LAMBDA(ref, ADDRESS(MIN(ROW(ref)),MIN(COLUMN(ref)),1,1)&":"&ADDRESS(MAX(ROW(ref)),MAX(COLUMN(ref)),1,1)}

then using =MAGIC(A1:B10) in the worksheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.