Results 1 to 3 of 3

INDIRECT(ADDRESS(...)) in ranges

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 365
    Posts
    349

    INDIRECT(ADDRESS(...)) in ranges

    I need to find the minimum value of a range which starts in C10 and ends n rows below. The number n is the result of a counter located in A1. I would expect following formula to work:
    MIN(C10:ADDRESS(($A$1+9),3))
    but it doesn't. I must write:
    MIN(C10:INDIRECT(ADDRESS(($A$1+9),3)))
    and I find that strange, because the INDIRECT-statement means the content of lowermost cell in the range and not the cell itself

    Can anyone explain why it works this way?

    The result shall appear in Sheet 'Dist' whith the raw data in sheet 'ChanA'. When I know the limits of the range I can write in sheet 'Dist':
    =MIN(ChanA!C10:C73)
    and that works perfectly. But my data varies and I have built in a counter in A1 to give the limit of the range, so I try with:
    =MIN(ChanA!C7:INDIRECT(ADDRESS($A$1+9,3)))
    but it results in an error.
    The same value is in A1 in both sheets.
    What can I do about it?


    NSV
    Last edited by nsv; 09-19-2007 at 05:24 AM.

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