Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    May 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Carrying data between worksheets (Excel 2003)

    Hi- I am trying to get my very manual process to be more automated. I have included a portion of my spreadsheet and the raw data where the info needs to pull from. The first tab is the end worksheet that needs to be filled in from the second tab. I have color coded where the information need to pull from to help understand. I need to pull the reorder qty, the qty on hand and the qty on PO. I need the qty on PO to also include the qty in transit. We have four locations (1,2,3, & 4)so if there is something in transit it shows under 11 or 22. The second tab is exactly how the information looks like after it is exported from our system. It is very rough and a coupld of the columns get shifted over, but they are all in the same spot for each part number. There will be more part numbers on the second tab than what is on the first tab. I only need the info for the items that are on the first tab.
    I know it sounds confusing so let me know if you have questions.

    Thanks for the help!
    Leticia

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Carrying data between worksheets (Excel 2003)

    If I understand correctly. It would have helped if you had filled in the appropriate cells so I could compare my values to yours. I matched the values in B3:B13 (A14:A24 values are not in the Raw Data sheet). the values in the other lookups are not filled in so I get values for them.

    First I would create an intermediate column (I will use column O) since it is empty. you could also insert this within your data set or even before. it may be hidden if desired. this intermediate calc will determine the row that the value in 'Reorder Spreadsheet'! col A cointains for each row.

    In 'Reorder Spreadsheet'!O3 enter the formula:
    <pre>=MATCH(TEXT(A3,"0"),'Raw Data'!A:A,0)</pre>


    [Note: text is needed since the lookup value in Raw data are text and the values in col A are numbers.]

    Copy O3 down the column as many rows as needed.

    Having this value, you can get the value for Reorder col B directly with an index. In B3 enter:
    <pre>=INDEX('Raw Data'!N:N,O3)</pre>


    Copy this down the column

    To get the values for D3 is a little more complicated. I created a new range using offset starting at the row in col O and have it lookup "1" (note that it is Text, not the number 1, just like the "raw data"). Once the row is obtained, this value is used to index col G and subtract the value from Col I (adding on the row value from Col O. In D3 enter the formula:
    =INDEX('Raw Data'!$G:$G,$O3+MATCH("1",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,65535-$O3,1),0))-INDEX('Raw Data'!$I:$I,$O3+MATCH("1",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,65535-$O3,1),0))

    Copy/Autofill D3 down the columns

    Col G, J, and M are nearly identical formulas. Each just replace the two values of "1" for the appropriate text to lookup:
    G3:
    =INDEX('Raw Data'!$G:$G,$O3+MATCH("2",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,65535-$O3,1),0))-INDEX('Raw Data'!$I:$I,$O3+MATCH("2",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,65535-$O3,1),0))
    Copy/Autofill G3 down the columns

    J3:
    =INDEX('Raw Data'!$G:$G,$O3+MATCH("3",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,65535-$O3,1),0))-INDEX('Raw Data'!$I:$I,$O3+MATCH("3",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,65535-$O3,1),0))
    Copy/Autofill J3 down the columns

    M3:
    =INDEX('Raw Data'!$G:$G,$O3+MATCH("4",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,65535-$O3,1),0))-INDEX('Raw Data'!$I:$I,$O3+MATCH("4",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,65535-$O3,1),0))
    Copy/Autofill M3 down the columns

    Hope this helps,
    Steve

  3. #3
    New Lounger
    Join Date
    May 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying data between worksheets (Excel 2003)

    Thanks for your help. This is what I needed. I had to take off some of the raw data info because the file was too big, so you were right some of the info is missing. The only think that I need to add is that the Qty on hand also needs to subtract out the Qty on sales order. It is column J on the raw data sheet.
    And last I also need to fill in the on order qty in my first spreadsheet. This number will be column H on the reorder sheet. The trick with this one is that if we have something in transit inbetween locations then I need it to be included in the on order qty. If we transfer something from location 1 to location 2 then it will show up on the line of 22. You can see an example of it on part number 47000012501000 row 85. we have 10,000 in transit (11) going to location 1. I would like to have that 10000 added to any on PO in cell J84. That total would then go into the On order column in the first spreadsheet Cell E7.

    Whew!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Carrying data between worksheets (Excel 2003)

    You have added a "complication" if you want to look for "11", "22", "33", or "44" since not all the "groups' have them and match would find the one that in "next", not indicate that it does not exist. To fix this, add an intermediate calc to indicate the "length" of the lookup. In P3:

    =MATCH("Total",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,3,65535-$O3,1),0)

    This looks for the "Total" to indicate the row number for the last row.

    Since in D3 you want Col G - Col I - Col J in the "group" from Col A, which has a "1" in Col E used this formula in D3:

    =INDEX('Raw Data'!$G:$G,$O3+MATCH("1",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0))-INDEX('Raw Data'!$I:$I,$O3+MATCH("1",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0))-INDEX('Raw Data'!$J:$J,$O3+MATCH("1",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0))

    Copy D3 down the column.

    If I understand, in Col E you want the value to lookup "11". If it is not found, instead of an error, I assume you want a zero. In E3 enter:
    =IF(ISNA(MATCH("11",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0)),0,INDEX('Raw Data'!$G:$G,$O3+MATCH("11",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0)))

    Copy E3 down the column.

    I presume you want similar in the other "sections" only instead of "1" and "11", you want the Location # for "on hand" and "##" for the "on order":

    Copy D3:E3 to G3:H3
    Edit the three "1"s in G3 to "2"s and edit the two "11"s in H3 to "22"s.
    Copy G3:H3 down the columns

    Copy D3:E3 to J3:K3
    Edit the three "1"s in J3 to "3"s and edit the two "11"s in K3 to "33"s.
    Copy J3:K3 down the columns

    Copy D3:E3 to M3:N3
    Edit the three "1"s in M3 to "4"s and edit the two "11"s in N3 to "44"s.
    Copy M3:N3 down the columns

    Is this what you are after?

    Steve

  5. #5
    New Lounger
    Join Date
    May 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying data between worksheets (Excel 2003)

    Column D, G, J & M are all correct. That is exactly what I wanted!

    In column E, H,K & N it needs to be the total on Po (Column J on raw datat sheet) plus the total on hand (column G)in the corresponding instransit location (22, 11, 33, or 44). So yes if there isn't any 11 or 22 etc then it does not need to do anything.

    I hand typed in the total for the first part number and highlighted the cells where I got it from on the raw data sheet so that you can see. I added 200 on the in transit (11) because it was 0 before.

    so total for on hand for 47000009300750 should say 5200. Cell J29 + G30 on raw data sheet.

    If there is not an in transit (11,22,33, or 44) for any part number then it would only bring over the qty in column J.

    Does that make sense?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Carrying data between worksheets (Excel 2003)

    If I understand:
    This will grab the item in the group in the row with "1" from Col J and add it to the item in the group from "11" (if it has one) from Col G. So in E3:
    =INDEX('Raw Data'!$J:$J,$O3+MATCH("1",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0))+IF(ISNA(MATCH("11",OFF SET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0)),0,INDEX('Raw Data'!$G:$G,$O3+MATCH("11",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0)))

    Copy down the column

    Copy E3 to H3, K3, N3 and edit to replace the "1"s with "2" (or "3" or "4") and the "11"s with "22" (or "33" or "44") as appropriate. Copy each down the columns.

    My formulas assume that 1,2,3,4 will always be in the "group", but that the 11,22,33,44 may not be (I only test for these). If that is not the case, an IF will be necessary like that for the "##"s

    Steve

  7. #7
    New Lounger
    Join Date
    May 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying data between worksheets (Excel 2003)

    That is it!!!!!!!!!!!!!!!!!!!!!!!!!! You have no idea how much easier this will make my work. Thank you soo much. I used to have to look up each part number (400 of them)and manually put in the qty's on a weekly basis.

    Thank you again for your time!
    Leticia

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Carrying data between worksheets (Excel 2003)

    You are very welcome. glad I could help...

    Steve

  9. #9
    New Lounger
    Join Date
    May 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying data between worksheets (Excel 2003)

    I found out that not all of the locations are listed for every part number. So where in this formula would I add the IF that you were talking about? I know you said like the "##"s. But this formula is way advanced for me and I can't figure out where to add it so that it will put a 0 if there isn't a 1 and 11 or 2 and 22 etc.

    =INDEX('Raw Data'!$J:$J,$O3+MATCH("1",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0))+IF(ISNA(MATCH("11",OFF SET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0)),0,INDEX('Raw Data'!$G:$G,$O3+MATCH("11",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0)))

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Carrying data between worksheets (Excel 2003)

    Does this work?

    =if(isna(MATCH("1",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0)),0,INDEX('Raw Data'!$J:$J,$O3+MATCH("1",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0)))+IF(ISNA(MATCH("11",OF FSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0)),0,INDEX('Raw Data'!$G:$G,$O3+MATCH("11",OFFSET(INDIRECT("'Raw Data'!A1"),$O3,4,$P3,1),0)))

    Steve

  11. #11
    New Lounger
    Join Date
    May 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying data between worksheets (Excel 2003)

    That's it.

    Thanks!

  12. #12
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying data between worksheets (Excel 2003)

    Hi all --need something similar but attachments are missing --can ne1 help
    thanx
    Smbs

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

    Re: Carrying data between worksheets (Excel 2003)

    Many attached files were lost with the recent Lounge server crash. So unless lalvarez has a copy of the files and happens to see your post, probably nobody will be able to help you. You would probably be better off starting a new thread and attaching a file that shows exactly what you want to do.
    Legare Coleman

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

    Re: Carrying data between worksheets (Excel 2003)

    In addition to Legare's comments, <!profile=bdesilva>bdesilva<!/profile> and <!profile=lalvarez>lalvarez<!/profile>, the authors of this thread and the one in VB/VBA you asked about, haven't been online since 2005, so there is little chance that they'll see your request. Posting a new question is probably more efficient.

Posting Permissions

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