Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #3  
Old 10-22-2024, 12:14 AM
ImAhNoBoDy ImAhNoBoDy is offline Cell Range to Address Array Windows 7 64bit Cell Range to Address Array Office 2007
Novice
Cell Range to Address Array
 
Join Date: Jan 2012
Posts: 17
ImAhNoBoDy is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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 View Post
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 View Post
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)))
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Using IF AND to filter number in a range, works with dragging, but not in an Array mda99das Excel 5 11-17-2022 01:27 PM
Loading an Excel range to an array in VBS BobBridges Excel Programming 1 12-25-2020 01:04 PM
Cell Range to Address Array Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow FUGMAN Excel Programming 7 02-05-2017 08:37 AM
Cell Range to Address Array Error: AGGREGATE_doesn't ignore hidden rows for particualr array range adisco Excel 1 01-10-2017 10:59 AM
Cell Range to Address Array If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C rick10r Excel 1 07-05-2016 12:07 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:48 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft