+ Reply to Thread
Results 1 to 7 of 7

How to define an array given row and column numbers?

  1. #1
    Registered User
    Join Date
    12-23-2010
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    66

    How to define an array given row and column numbers?

    Hello,

    How do I define an array by using row and column numbers?

    What I want to do is:

    =product(array)

    in which the starting and ending cell are defined by row/column numbers.

    For example:
    Row 1, Column 1 = Cell A1
    Row 3, Column 5 = Cell E3

    I want to calculate '=product(A1:E3)' without using the term A1:E3 but instead define A1 and E3 using Row/Columns numbers.
    Thanks!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to define an array given row and column numbers?

    Not sure what you're up to.

    Why would you do it that way.

    Maybe you want something like in this example?
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    12-23-2010
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: How to define an array given row and column numbers?

    Quote Originally Posted by oeldere View Post
    Not sure what you're up to.

    Why would you do it that way.

    Maybe you want something like in this example?
    Based on a value in a cell, I want Excel to calculate the product of an array. The location and size of the array depends on the value in the orginal cell. That's why.
    Last edited by anon; 08-22-2012 at 03:13 PM.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to define an array given row and column numbers?

    Please also respond on my example (is it usefull)?

    and even better, make an excel example without confidentional information and the wanted result.
    Last edited by oeldere; 08-22-2012 at 03:13 PM.

  5. #5
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: How to define an array given row and column numbers?

    Let's define an array using rows and columns. Consider this UDF:

    Please Login or Register  to view this content.
    In the worksheet, the formula:

    =makearray(1,1,3,3)
    will display:
    $A$1:$C$3
    Gary's Student

  6. #6
    Registered User
    Join Date
    12-23-2010
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: How to define an array given row and column numbers?

    Quote Originally Posted by oeldere View Post
    Please also respond on my example (is it usefull)?

    and even better, make an excel example without confidentional information and the wanted result.
    Thansk for the help. Unfortunately I don't think that's what I'm looking for.
    Quote Originally Posted by Jakobshavn View Post
    Let's define an array using rows and columns. Consider this UDF:

    Please Login or Register  to view this content.
    In the worksheet, the formula:

    =makearray(1,1,3,3)
    will display:
    $A$1:$C$3
    This is what I'm looking for except I've never used Macros before. Can you product the array you created? What's the easy way to add this Macro?

  7. #7
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: How to define an array given row and column numbers?

    We change the UDF to perform all the multiplications:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1