Quote:
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
Code:
=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
Code:
=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.
Quote:
Originally Posted by p45cal
Code:
=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.
Quote:
Originally Posted by p45cal
Otherwise, a user-defined function:
Code:
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:
Code:
=TEXTJOIN(",",TRUE,ADDRESS(ROW(A1:E1),COLUMN(A1:E1)))
10-22-2024, 12:14 AM
Similar Threads
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow
Windows 7 64bit 
Office 2007
Threaded Mode