+ Reply to Thread
Results 1 to 4 of 4

Recall relative cell by dropdown list on separate sheet

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    Brawley, California
    MS-Off Ver
    Excel 2010, 2007
    Posts
    11

    Recall relative cell by dropdown list on separate sheet

    Hello friends,

    I have a dropdown list (data validation) whos values may span several columns on another sheet. I need to reference cells in other columns on the same sheet, I have color coded these for the purpose of their appropriate associations. Please refer to my attached workbook. I can write this in VBA, but there must be a simpler way using references. I don't need the cell's value, only the A1:B2 references.

    Also, How would I write an offset formulahelpmebook_Please.xlsx so that I could use the range produced as the 'COLS' section of the formula?

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Recall relative cell by dropdown list on separate sheet

    Your first question:

    D5: =ADDRESS(4, MATCH(B5, Sheet2!$7:$7, 0) - 1)
    E5: =ADDRESS(4, MATCH(B5, Sheet2!$7:$7, 0) + COUNTIF(Sheet2!$7:$7, B5) -2)



    It would help if it was known what the range is going to be used for. It might simplify the needed answer.

    This formula creates the "range" of required cells from B4:G4 in a single formulaic answer:

    =OFFSET(INDEX(Sheet2!$7:$7, MATCH($B$5, Sheet2!$7:$7, 0)), -3,-1, ,COUNTIF(Sheet2!$7:$7, $B$5))

    But since there is no use context, it just returns the first value from that range, B4. If you were to use that formula to populate a Data Validation list in B4, then you would see all the dates in the drop down.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-15-2012
    Location
    Brawley, California
    MS-Off Ver
    Excel 2010, 2007
    Posts
    11

    Re: Recall relative cell by dropdown list on separate sheet

    Jerry,

    Thanks for the help I will impliment it and see if it works in my application. The use of the offset function creates a table of the analyte data from the third page using the called range as the first column. In this way we can see what analyte results for the particular analyst were reported based upon the sampling time of the analyte (thus the reference to the previous column data). Rockets? Really! I am into Genetics.

    BAT:-)

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Recall relative cell by dropdown list on separate sheet

    If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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