+ Reply to Thread
Results 1 to 7 of 7

How to display using excel formulae details of one row when entering only one data

  1. #1
    Registered User
    Join Date
    07-20-2009
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    30

    How to display using excel formulae details of one row when entering only one data

    Gentleman,

    I have a problem filling up time cards of personnel. To expedite the typing , I tried to use excel formulae to help me expedite my work.

    I am looking for a way that whenever I type the Tally No. of the employee for each day of the month. I get in the same row, the balance fields which includes the name of the person, company details etc.

    I managed to get the above but it comes only in one cell. I need it for all the personnel on daily basis when I type in the Tally No. I have attached the example in the excel sheet.

    Thanking you in advance

    Regards
    Pio Desousa Proenca
    Attached Files Attached Files

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: How to display using excel formulae details of one row when entering only one data

    =INDEX($P$4:$P$165,MATCH(E2,$Q$4:$Q$165,0))

    note the index range
    just changing the column range to return desired result
    i.e.

    =index(R4:R165,same match formula above)
    here R4:R165 is the location of the Company Name so it will return company name.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: How to display using excel formulae details of one row when entering only one data

    hi there. could you make the headers in D1, F1, G1 & H1 the same with the headers from P3? then you just need this formula to copy down & across:
    =INDEX($P$4:$Y$165,MATCH($E2,$Q$4:$Q$165,0),MATCH(D$1,$P$3:$Y$3,0))

    otherwise, you got to change in every column. in D2:
    =INDEX($P$4:$P$165,MATCH($E2,$Q$4:$Q$165,0))
    in F2:
    =INDEX($R$4:$R$165,MATCH($E2,$Q$4:$Q$165,0))
    and so on.
    Attached Files Attached Files
    Last edited by benishiryo; 02-05-2013 at 08:11 AM. Reason: added file

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    07-20-2009
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: How to display using excel formulae details of one row when entering only one data

    Gentleman,

    I tried the above formualae. But I could not get. Could you please insert the above formula in my attached excel sheet.

    Thanks & Regards
    Pio

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: How to display using excel formulae details of one row when entering only one data

    look here.

    pio.xlsx

  6. #6
    Registered User
    Join Date
    07-20-2009
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: How to display using excel formulae details of one row when entering only one data

    Thank you, thank you , this is of great help

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: How to display using excel formulae details of one row when entering only one data

    Your very much welcome.
    Can you mark the thread "Solved".
    How?
    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

    Note:
    You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
    By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.



    Regards,
    Vladimir

+ 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