Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Pull in amounts from one worksheet to another (XP & 2003)

    I am trying to pull amounts from one worksheet to another worksheet. I am attaching a workbook "test-dollar and hr changes"Specifically, I would like bring into this workbook tab FTE the STD HRS (column G) and the Rate/Hr (column H) from the worksheet tab Data. Based on File No (column F) and Month (Row 8). There are different months moving accross the worksheet.

    In the Data worksheet, there may be duplicate STD HRS or Rate/Hr per a File No record. That is because the Rate/Hr may have changed but the Std Hrs did not or vice-versa.

    Is there any feasible formulas to accomplish this goal. I tried index-match and conditional summing(just a shot) but I am not successful with any of them.

    The last point is each cell must have an amount, albeit a 0 .

    Thanks ,

    Stumped ???

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull in amounts from one worksheet to another (XP & 2003)

    The first problem that you are going to have to solve is that on the data sheet column A is text, and on the fte sheet column F is numeric. No formula is going to work until they are both the same, in this case text. After you have fixed that problem, then you will need to give us a better explanation of how you want to handle those duplicate records on the data sheet. Exactly what is used to pick between the duplicates?
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pull in amounts from one worksheet to another (XP & 2003)

    If I understand correctly, the months in the FTE sheet are 7 and 8. There are no records in the Data sheet with "mo of change" = 7 or 8, so there is no match at all. Or do I misunderstand?

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Pull in amounts from one worksheet to another (XP & 2003)

    OOPS, YOU ARE CORRECT. I REDUCED A MUCH LARGER SPREAD SHEET TO FIT THE 100K MAX. ATTACHMENT CONSTRAINT AND FORGOT TO CHANGE THE MONTH OF THE CHANGES. REVISED AND NOW ATTACHED

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Pull in amounts from one worksheet to another (XP & 2003)

    As far as the duplicates go, I believe I will have to manually go through the list and delete the appropriate item. I will study this more.

    How did you determine that one set is numeric and one is text? They both looked like text especially with the prefix 0's. I corrected this by changing everything to text with the data /text to column command.

    Does this help?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pull in amounts from one worksheet to another (XP & 2003)

    Puhleeze stop using all caps! How many times have I asked that now? <img src=/S/cranky.gif border=0 alt=cranky width=18 height=25>

    I have inserted a column to the left of column H in the data sheet; you can hide this column if you like. This is used in VLOOKUP formulas in the fte sheet. See attachment.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Pull in amounts from one worksheet to another (XP & 2003)

    Dear Hans,

    I am sorry that the Caps are disturbing to you. Honestly, It is a habit caused by lazy pinky fingers. It is not a sign of shouting or yelling. I will try to break this habit. Please have a little patience. I am a fairly quiet person.

    I do want to take the opportunity to thank all of you at woody's Lounge. I am learning quite a bit and applaude all of you for you time, patience, expertise, etc...

    I hope I can assist in answering some questions in the future.

    Once again, Thanks and I promise I'll try to be not so cap crazy. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •