1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Transfer of data from different excel to main excel

Discussion in 'Business Applications' started by Mayank1322001, Feb 2, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Mayank1322001

    Mayank1322001 Thread Starter

    Joined:
    Feb 2, 2010
    Messages:
    6
    Please help ,

    I have one sheet with headings,with 50 more sheets which include data,in data we have one emp code which is unique and more data for that emp.,format in all the sheets is same except the summary sheet(main sheet),what i want when i enter the Emp code in suumary sheet,the excel automatically search the data from subsquent sheet (basis emp code)and enter the data in next columns against emp code in summary sheet.

    Regds
    Mayank s
    +919376914028,
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,756
    First Name:
    Wayne
    so you want to look up 50 different sheets for the data -
    In your example

    empcode = CTC
    do you then want
    50 entries go into the summary sheet

    in your example
    you have
    Code:
    CTC - DS1  - 10000
    CTC - DS2 -  10050
    
    how do you want the results displayed ?

    i would remove phone number - no idea who will get that .... lots of robots trawl forums for emails etc - to use as spam
     
  3. Mayank1322001

    Mayank1322001 Thread Starter

    Joined:
    Feb 2, 2010
    Messages:
    6
    As in first sheet named Summary

    when i enter the Emp code in column empcode,next columns named CTC,Basic etc filled automatically by finding the data for respective emp.

    also each emp code have seperate sheet like i had shown.

    Thanks
    waiting for help
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,756
    First Name:
    Wayne
    OK - I see each Sheet name is the Empcode and also B1 has the Empcode

    any reason for the structure of the data onto seperate sheets ??
    shame its not structured all on one sheet
    Code:
    Headers
    empcode CTC Fuel conv Basic
    
    then a simple vlookup would provide all the results - perhaps we can use a indirect and lookup using ,INDIRECT(ref for worksheet name&"!A1:F100"),

    =VLOOKUP($B$2,INDIRECT(A2&"!A2:b5"),1,0)

    will additional sheets be added at any time ?
    Is the format always the same ?
    Do you update the sheets at all?
     
  5. Mayank1322001

    Mayank1322001 Thread Starter

    Joined:
    Feb 2, 2010
    Messages:
    6
    will additional sheets be added at any time ?--yes additional sheets will be added as the new emp came


    Is the format always the same ?--yes always same

    Do you update the sheets at all? No sheets will not updated once entered.
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,756
    First Name:
    Wayne
    So in summary Cell B2

    =VLOOKUP(B$1,INDIRECT("'"&$A2&"'"&"!a2.b5"),2,FALSE)

    then copy across to Cell C2, D2

    and you should be able to copy down as well

    what we are doing is using vlookup to lookup the value against CTC, Basic etc
    so in bold
    =VLOOKUP(B$1,INDIRECT("'"&$A2&"'"&"!a2.b5"),2,FALSE)
    goes to the your header in the summary sheet to look at the value to lookup, CTC , Basic , etc

    next
    =VLOOKUP(B$1,INDIRECT("'"&$A2&"'"&"!a2.b5"),2,FALSE)
    this bold looks at your empcode entry and is then used to complile a sheet and range reference for the look up
    like this
    =VLOOKUP(B$1,INDIRECT("'"&$A2&"'"&"!a2.b5"),2,FALSE)

    then indirect
    is used to keep the values found in the formula

    =VLOOKUP(B$1,INDIRECT("'"&$A2[&"'"&"!a2.b5"),2,FALSE)

    when you find CTC, or Basic etc in the sheet - return whatever is in the 2nd column
     
  7. Mayank1322001

    Mayank1322001 Thread Starter

    Joined:
    Feb 2, 2010
    Messages:
    6
    still unable to get data,shoul indirect function has to be declared some where else also

    please help,
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,756
    First Name:
    Wayne
    No

    i will upload a sample

    what this post for an edit
     

    Attached Files:

  9. Mayank1322001

    Mayank1322001 Thread Starter

    Joined:
    Feb 2, 2010
    Messages:
    6
    ok thanks a ton
     
  10. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,756
    First Name:
    Wayne
    did that work OK ?
     
  11. Mayank1322001

    Mayank1322001 Thread Starter

    Joined:
    Feb 2, 2010
    Messages:
    6
    Yes dear it works

    and its simply great,it helps me a lot and had solved my prob. completly,

    its amazing ..thanx a ton...billion
    regds,

    mayank S
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/899623

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice