+ Reply to Thread
Results 1 to 7 of 7

How do I return a header of column based on a range of cells containing specific data?

  1. #1
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

    How do I return a header of column based on a range of cells containing specific data?

    I have two spreadsheets. On one of them it has a column that I want to input a formula that will look at the 2nd sheet and in a specific range of cells looks for "12-13'". If one of those cells contains that data, I want the return value to be the header of the column that the data was found in.

    This is the formula I have tried and it doesn't work, any suggestions. I want the formula in Sheet 1 F3 and I will drag it down once it works.

    =IF('Sheet2'!$D3:$J3<>"12-13'",INDEX('Sheet2'!$D$2:$J$2,3,MATCH("12-13'",'Sheet 2'!$D3:$J3,0)),"")

    Test Spreadsheet.xlsx
    Last edited by Dena; 07-26-2012 at 04:29 PM. Reason: Attaching Spreadsheet

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,965

    Re: How do I return a header of column based on a range of cells containing specific data?

    Try this user definded function

    Please Login or Register  to view this content.
    Open the VBA editor by hitting Alt F11.
    Insert a new module with Insert - Module
    Paste in the above function
    Go back to the sheet by hitting Alt F11.

    In Sheet2 F3, enter =GetCycleYear(B3,D3,Sheet1!A:D,Sheet1!$2:$2)

    Remember to save the workbook as a macro enabled workbook .xlsm
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

    Re: How do I return a header of column based on a range of cells containing specific data?

    That didn't seem to work, it converted everything to a R3-Year 3-Reg which in accurate. Does dragging the formula down the sheet not work with this type of thing. I have never used macros.

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,965

    Re: How do I return a header of column based on a range of cells containing specific data?

    Should behave like a normal formula. Is your calculation set to automatic?

  5. #5
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

    Re: How do I return a header of column based on a range of cells containing specific data?

    I have no idea but I think I know what the problem might be. You have it as =GetCycleYear(B3,D3,Sheet1!A:D,Sheet1!$2:$2) and D3 is not where I want it looking for 12-13'. I actually could remove that column. It needs to pull the info from sheet 1. Maybe I am reading this incorrectly.

  6. #6
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,965

    Re: How do I return a header of column based on a range of cells containing specific data?

    Please see the attachment.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

    Re: How do I return a header of column based on a range of cells containing specific data?

    I guess I did something wrong. Thank you so much, it's working.
    Last edited by Dena; 07-26-2012 at 07:04 PM.

+ 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