Cell Range to Address Array

[ImAhNoBoDy]

Is there a way to get an range and then get all the addresses of the range?

The range would be A1:E1, the output would be $A$1,$B$1,$C$1,$D$1,$E$1.

[ArviLaanemets]

Select the range A1:E1, and define it as named range, e.g. with name as nMyRange

To get a value from nMyRange, enter into cell, where you want to calculate a value from mMyRange, the formula

=INDEX(nMyRange, x)

, where x is any expression which returns either 1, 2, 3, 4, or 5 - i.e. the formula returns the x'th value from nMyRange.

To get the address of x'th cell in nMyRange, use the formula like

=ADDRESS(ROW(INDEX(nMyRange,x)),COLUMN(INDEX(nMyRange,x)))

[ImAhNoBoDy]

Originally Posted by ArviLaanemets

Select the range A1:E1, and define it as named range, e.g. with name as nMyRange

To get a value from nMyRange, enter into cell, where you want to calculate a value from mMyRange, the formula

=INDEX(nMyRange, x)

, where x is any expression which returns either 1, 2, 3, 4, or 5 - i.e. the formula returns the x'th value from nMyRange.

To get the address of x'th cell in nMyRange, use the formula like

=ADDRESS(ROW(INDEX(nMyRange,x)),COLUMN(INDEX(nMyRange,x)))

This only gives me 1 address when I try it. I'm not evaluating any values though, when generating the address array.

Originally Posted by p45cal

=REDUCE("",A1:E1,LAMBDA(a,b,TEXTJOIN(",",TRUE,a,CELL("address",b))))

This solution does work for Excel 365 products. I'm using Excel 2021 and I don't have the LAMDA or REDUCE functions.

Originally Posted by p45cal

Otherwise, a user-defined function:

Function addresses(rng As Range) As String
For Each cll In rng.Cells
  addresses = addresses & "," & cll.Address
Next cll
addresses = Mid(addresses, 2)
End Function

This solution also works, but Polaris Office on my tablet for whatever does not display the address array. So what I do is edit the spreadsheet on my computer and transfer it to my tablet to work on.

As I was searching for another solution, I ended up on a site that creates excel formulas using AI. It came up with this and it works:

=TEXTJOIN(",",TRUE,ADDRESS(ROW(A1:E1),COLUMN(A1:E1)))

[p45cal]

Originally Posted by ImAhNoBoDy

As I was searching for another solution, I ended up on a site that creates excel formulas using AI. It came up with this and it works:

=TEXTJOIN(",",TRUE,ADDRESS(ROW(A1:E1),COLUMN(A1:E1)))

Excellent. One of those too few occasions when AI produces a sensible result. Could you provide a link to that site please?

ps, you could use 4 as the ADDRESS function's 3rd argument to lose the $ symbols too.

[ImAhNoBoDy]

Originally Posted by p45cal

Excellent. One of those too few occasions when AI produces a sensible result. Could you provide a link to that site please?

ps, you could use 4 as the ADDRESS function's 3rd argument to lose the $ symbols too.

The site I used was this:

https://aiexcelbot.com/formulas/logical/and

[p45cal]

=REDUCE("",A1:E1,LAMBDA(a,b,TEXTJOIN(",",TRUE,a,CELL("address",b))))

2024-10-21_101625.jpg

[p45cal]

Otherwise, a user-defined function:

Function addresses(rng As Range) As String
For Each cll In rng.Cells
  addresses = addresses & "," & cll.Address
Next cll
addresses = Mid(addresses, 2)
End Function

2024-10-21_103925.jpg

[Pecoflyer]

I'm using Excel 2021 and I don't have the LAMDA or REDUCE functions.

As your profile says you use XL2007, you could perhaps start by updating it ?