+ Reply to Thread
Results 1 to 8 of 8

Formula to identify with row&column

  1. #1
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Formula to identify with row&column

    Morning,

    Which is the best formula to identify where data has come from, for example i have some data in cell B2 but i need to identify where that data is on another sheet.

    Data in B2 is on a sheet call Current Month's Template and the source data is on a sheet called PDQ

    I have tried the following;

    =row(Current Month's template!B2,PDQ!A:Z,1,0)

    This doesn't seem to work

    Any ideas

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to identify with row&column

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Formula to identify with row&column

    As requested, the formula will be on the 'Data type sheet'
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Formula to identify with row&column

    bump - no repsonse

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Formula to identify with row&column

    Without looking at your workbook, that formula looks as though it was once part of a VLOOKUP. That would determine the row where it finds a match and return the matching entry. If it is/was a VLOOKUP, it's just going to return what it searched for in column A.

    To find the row, use MATCH.

    Note that it is not good practice to use VLOOKUP with whole column ranges.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


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

    Re: Formula to identify with row&column

    @Trevor:
    Please Login or Register  to view this content.
    Can you cite the source of that belief? Thanks.
    _________________
    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!)

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Formula to identify with row&column

    @JB: No ... senior moment. Thinking of SUMPRODUCT for some reason. Oops. Thanks for highlighting the unfounded belief.

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Formula to identify with row&column

    Hi Ken

    Have no idea where your going or how you're going to get there...perhaps this'll get you started. This UDF (User Defined Function) will populate Sheet DataSource Cell C3 with "M8".

    Place this Code in a Standard Module
    Please Login or Register  to view this content.
    Copy/Paste this Formula in Sheet DataSource Cell C3 then Enter...C3 now contains M8
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

+ 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