+ Reply to Thread
Results 1 to 7 of 7

Formula for finding text in sheet1 with numbers from sheet2

  1. 06-17-2013, 02:59 PM #1
    excelresumes
    excelresumes is offline
    Registered User
    Join Date
    06-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Formula for finding text in sheet1 with numbers from sheet2

    Firstly, I am grateful for any responses about this as I am clueless.

    I work for a nonprofit company for disabled adults and we have the staffing on an excel sheet.

    We have 3 excel sheets setup:

    Sheet1 is a list of the Clients in ROW A, then we have different names of Staff working with each client Monday-Sunday.

    Sheet2 is a list of the Clients in ROW A, and then instead of names of Staff we have numbers representing how many hours the staff has worked for that day.

    Sheet3 is a list of the Staff in ROW A, I need a formula or something that will search for all Staff in Sheet1 and correspond it with the numbers that the cells they are on from Sheet2 and then add up all instances of each name/number into Sheet3.

    I'm really sorry if that's confusing, I am very new to excel and that's the best way i could explain what we need.

    Thanks in advanced for any help, I'll try to respond ASAP to any questions.


    -Steven
    Reply With QuoteRegister To Reply
  2. 06-17-2013, 03:10 PM #2
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Formula for finding text in sheet1 with numbers from sheet2

    Hi Steven, welcome to the forum.

    Your requirements sound reasonably straightforward. You should upload a small, sample workbook, without sensitive data - while there are many here happy to help, many of us are disinclined to make up a sample workbook as a precursor to assisting. 5/10 names/numbers in each list should be plenty to work with.

    Hope this helps.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.
    Reply With QuoteRegister To Reply
  3. 06-17-2013, 03:32 PM #3
    excelresumes
    excelresumes is offline
    Registered User
    Join Date
    06-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula for finding text in sheet1 with numbers from sheet2

    client_staff.xlsx

    Here is an upload with fake names added, but this is exactly what our current workbook looks like.
    Reply With QuoteRegister To Reply
  4. 06-17-2013, 04:02 PM #4
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Formula for finding text in sheet1 with numbers from sheet2

    I copied all the info to one tab, as it's easier to make sure calculations are correct etc without having to flip between tabs. I suspect what you really need is a SUMPRODUCT formula, which one of the clever guys/gals here may yet provide, but in the absence of that, the following should work for you. This formula:

    Please Login or Register  to view this content.
    in T2, and copied down, looks for the employees names, then sums the corresponding figure/s from the hours worked data.

    So, what 
    Please Login or Register  to view this content.
    really says is, look in B2 to B11 for a match for the contents of S2, and sum the numbers from the corresponding cells in K2 to K11. Then I've just added extra cell ranges for the other days of the week.

    Hope this helps.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 06-17-2013, 05:45 PM #5
    excelresumes
    excelresumes is offline
    Registered User
    Join Date
    06-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula for finding text in sheet1 with numbers from sheet2

    Thank you so much for this!
    I am still not quite understanding it completely, but it looks like its exactly what I needed.. I will be working on this over the next two days and let you know if everything worked out.
    Thanks again! You are awesome!

    -Steven
    Reply With QuoteRegister To Reply
  6. 06-18-2013, 05:28 PM #6
    excelresumes
    excelresumes is offline
    Registered User
    Join Date
    06-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula for finding text in sheet1 with numbers from sheet2

    Just wanted to leave an update, that formula worked great and the scheduling is very efficient now.
    Thank you so very much for the help!
    Reply With QuoteRegister To Reply
  7. 06-19-2013, 08:32 AM #7
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Formula for finding text in sheet1 with numbers from sheet2

    Thanks for the update, Stephen - glad to hear this is working out for you.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1