+ Reply to Thread
Results 1 to 7 of 7

Indirect within a Minimum

  1. #1
    Registered User
    Join Date
    06-13-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Indirect within a Minimum

    I have a script

    =MATCH(H2,INDIRECT("'SALES'!A"&R2&":CC"&R2),0)

    to produce the column number of the minimum value on the SALES sheet. The issue is that when I change the minimum value it produces a completely wrong number and reads the wrong column. How would I get this to read only specific columns and find the minimum: L, W, AH, AS.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Indirect within a Minimum

    First, we can do this without indirect...

    This will be the equivelent of the Indirect portion of your formula
    INDEX('SALES'!A:CC,R2,0)

    So we have
    =MATCH(H2,INDEX('SALES'!$A:$CC,R2,0),0)


    Now, I don't see how your formula finds the Minimum value in the row...
    Does H2 have a MIN formula in it, referring to the same range?

  3. #3
    Registered User
    Join Date
    06-13-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Indirect within a Minimum

    Yes H2 does have a MIN formula within it and it solves it correctly when the minimum is changed in the Sales Sheet

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Indirect within a Minimum

    Quote Originally Posted by ac847 View Post
    Yes H2 does have a MIN formula within it and it solves it correctly when the minimum is changed in the Sales Sheet
    So it solves it correctly then?
    What is the issue?


    Can you post a sample book?

  5. #5
    Registered User
    Join Date
    06-13-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Indirect within a Minimum

    =MIN('SALES'!L65,'SALES'!W65,'SALES'!AH65,'SALES'!AS65) is H2. There is no issue with H2. The issue is that in this spreadsheet I need the minimum value to be solved and then produce the cell that is 6 to the left of the minimum. I am able to get the cell 6 to the left produced the first time, but when I change the minimum value, it screws the whole value up. I need it to search for the minimum values in specific cells.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Indirect within a Minimum

    Lost without an example (Go Advanced>Manage Attachments)

    What is in R2? and why?
    If you always want the value 6 to the left of the minimum (and that minimum value isn't duplicated elsewhere in row 65),
    =INDEX(Sales!$F$65:$AS$65, MATCH(H2, Sales!$L$65:$AS$65,0))
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Indirect within a Minimum

    post a sample book, there must be an easier way.

    Do you have Headers in Row 1 that might indicate which cells to look at ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 04-17-2013, 03:53 PM
  2. Sum of indirect sheet names with multiple cells (SUM, INDIRECT,SHEETNAME in cell)
    By a1b2c3d4e5f6g7h8 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-12-2013, 08:42 AM
  3. Find minimum SUM if no minimum number in row
    By zbor in forum Excel General
    Replies: 9
    Last Post: 12-25-2009, 05:04 AM
  4. [SOLVED] How to lookup the minimum, 2nd minimum and 3rd minimum.........
    By Mark McDonough in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-15-2006, 04:45 PM
  5. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Andy Wiggins in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 04:05 AM

Tags for this Thread

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