+ Reply to Thread
Results 1 to 2 of 2

Change column reference while dragging formula vertically

  1. #1
    Registered User
    Join Date
    06-22-2012
    Location
    Tacoma, WA
    MS-Off Ver
    Excel 2013
    Posts
    4

    Change column reference while dragging formula vertically

    Hello there,

    I have seen a few instances of solutions for this type of problem, but because my formula is already a bit complex (for me at least!) I am having trouble replicating the solution. I am asking a cell to go to another sheet, find the max value in a list, and then give me a corresponding value from another column. In the attached workbook, you can see what I have done in K10 on the first sheet:

    =INDEX('Peer Data Set (NEW)'!$C$5:$C$10,MATCH(MAX('Peer Data Set (NEW)'!K$5:K$10),'Peer Data Set (NEW)'!K$5:K$10,0))

    That worked and I was happy.

    Now, I want to drag that down and have it populate with "L" meanwhile leaving everything else as is, then with "M," then "N," etc.

    Any assistance would be great. I do not do well with macros. I know, I knoForum Example 6.17.13.xlsxw.....

    Thanks!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Change column reference while dragging formula vertically

    I didn't look at your file...

    If you're entering the 1st formula in cell K10 and copying down:

    =INDEX('Peer Data Set (NEW)'!$C$5:$C$10,MATCH(MAX(INDEX('Peer Data Set (NEW)'!K$5:N$10,0,ROWS(K$10:K10))),INDEX('Peer Data Set (NEW)'!K$5:N$10,0,ROWS(K$10:K10)),0))

    Adjust the range references to suit.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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