+ Reply to Thread
Results 1 to 4 of 4

Proper Syntax for filling out a template on Sheet2 based upon a row number from Sheet1

  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    Auburn Hills, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    2

    Proper Syntax for filling out a template on Sheet2 based upon a row number from Sheet1

    I have a Receipt template in Sheet2. Each row in Sheet1 contains the necessary information to fill in the Receipt template on Sheet2. As an input on Sheet2, in location $C$2, the user enters the Row number on Sheet1 that contains the necessary information for this particular receipt. In this simplified example, location B3 in Sheet1 contains the word Hurrah! I am having trouble with the syntax of a formula expression on Sheet 2 that places the value of B3 on Sheet1 in location D4 on Sheet2, given that the value of the desired row on Sheet1 has been input into $C$2 on Sheet2. I always know the proper column on Sheet1, so if I can do it for this simplified example I can easily create formulas for the remaining items on the receipt template in Sheet2. I have tried numerous TEXT and other formula types but the specific syntax eludes me. Can anyone help?

    A B C D
    1
    2
    3 Hurrah!
    4
    ---------------------------------------
    SHEET1

    A B C D
    1
    2 3
    3
    4 ????? What formula will pull the word Hurrah from Column B on Sheet 1 using the location at $C$2 as the row number on Sheet 1?
    ---------------------------------------
    SHEET2


    Thanks for your help!

  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: Proper Syntax for filling out a template on Sheet2 based upon a row number from Sheet1

    You can use INDEX() match for this.

    =INDEX(Sheet1!B:B, $C$2)

    With this, the column B is indexed, then the value in the position listed in cell C2 is pulled out of that index.
    _________________
    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
    07-09-2013
    Location
    Auburn Hills, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Proper Syntax for filling out a template on Sheet2 based upon a row number from Sheet1

    Thanks. Your suggestion worked fine. This is the first time I've used the Forum and you have made a believer out of me! I probably tried every function but the INDEX. I should have checked here sooner and next time I will.

  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: Proper Syntax for filling out a template on Sheet2 based upon a row number from Sheet1

    Glad that's working for you.

    Another method is to use VLOOKUP where the value in C2 would be a value from the Sheet1 column A, like a code or a name or some piece of unique data. With this, you don't have to know the exact row number yourself. You put in "John Doe" in C2 and VLOOKUP finds "John Doe" in column A and brings back the matching value from column B.

    =VLOOKUP($C$2, Sheet1!$A:$B, 2, 0)

    To stay with INDEX but go to this same "dynamic lookup" method, we replace the second parameter with a MATCH() function:

    =INDEX(Sheet1$B:B, MATCH($C$2, Sheet1!$A:$A, 0))


    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above and mark this 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)

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