Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    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: Transfer Info to another sheet (Excel 2002 (XP))

    I don't exactly understand what item(s) you want to move.

    I can understand how the name will match the sheet, but what in the data indicates that only row 7 goes into Christine's sheet and not any of the other "Christine rows". Are you looking at the name and a "Denied" in one of (several columns)? If so can the Denied be anywhere or just in Cols E, F or H?

    One comment:
    I am not a proponent of having multiple sheets with "identically formatted data". This only leads (in my experience) to confusion in slecting sheets, problems with maintaining records and much more work if summary info is needed.

    It makes more sense to me to keep it as you have it: You can use a pivot table to summarize the info, you can use autofiltering to display particular records, etc.

    If, for example you want to display Christine records that are denied, you could:
    Add a column that checks for a Denied in Cols, E, F or H
    [eg in I5 enter:
    =OR(ISNUMBER(SEARCH("Denied",E5)),ISNUMBER(SEARCH( "Denied",F5)),ISNUMBER(SEARCH("Denied",H5)))
    copy it down the columns into all the rows]

    Then you can autofilter on this column and christine to display christine's denied items. No need to move at all

    Steve

  2. #2
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    Insert>>Name>>Define
    Names in Workbook , enter : filename
    Refers to window box , enter: =GET.CELL(62,INDIRECT("rc",FALSE))

    Sheetname - Brandee
    In cell B4, array formula copy and across down :
    {=IF(ROW()-ROW(B$4)+1<=COUNTIF('Main Log'!$C$5:$C$100,MID(filename,FIND("]",filename)+1,255)),INDEX('Main Log'!$B$5:$H$29,SMALL(IF('Main Log'!$C$5:$C$29=MID(filename,FIND("]",filename)+1,255),ROW('Main Log'!$C$5:$C$29)-4),ROW(1:1)),MATCH(Brandee!B$3,'Main Log'!$B$4:$H$4,0)),"")}

    Sheetname - Christine, Yvonne & Susan
    In cell B3, array formula copy and across down :
    {=IF(ROW()-ROW(B$3)+1<=COUNTIF('Main Log'!$C$5:$C$100,MID(filename,FIND("]",filename)+1,255)),INDEX('Main Log'!$B$5:$H$29,SMALL(IF('Main Log'!$C$5:$C$29=MID(filename,FIND("]",filename)+1,255),ROW('Main Log'!$C$5:$C$29)-4),ROW(1:1)),MATCH(Brandee!B$3,'Main Log'!$B$4:$H$4,0)),"")}

    Hope that helps

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

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    In the attached workbook, click on the "Build Rep Sheets" button and see if it does what you want. The code is in the BuildRep procedure in Module1.
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    Bosco,

    I copied what you have here but the info is not pulling from the Main Log. Did I miss a step?

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

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    Have you tried my solution?
    Legare Coleman

  6. #6
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    Hi Legare,
    Thank you so much for the suggestion.
    Sadly, I am a greenhorn. I believe that might be easy to do but I am unsure how to procede...

  7. #7
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    Good Day All,
    Forgive my time difference.
    Thank you for the all the info. I am in agreement with that this form could possibly create an issue if I were trying to do a summary. I was hoping to add the summary page also. The info on each rep's sheet is the info that must be emailed to the rep as an update. In creating a spreadsheet that did an autofill to each separate rep, I thought I could save the user from having to enter the same info into many different sheets to email to to each rep.

    I will look at how I have my Main Log set up and maybe that would minimize the issues I may face when adding the summary page.
    You have enlightened me!
    Thank you so much,

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

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    Just download the file and click on the button in the upper left corner of the "Main Log" worksheet.
    Legare Coleman

  9. #9
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    I got that far <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> LOL

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

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    Were the Rep sheet that were created what you were looking for?
    Legare Coleman

  11. #11
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    I see the worksheets in VBA but I am not educated enough to build the worksheets.
    I am at elementary level. :-) It looks as though this would be ok for me once I learn but the other users I am making this workbook can type and that's as far as it goes for them. I want it to be a time saver for the user.
    Entering their daily work into one sheet that will autofill on separate sheets that are to be emailed to the individual reps. Otherwise the user will need to retype the same info up several times to send to the individual reps.
    Sounds like I'm making this complicated huh?
    Sorry.

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

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    I really don't understand what you are saying. I think my workbook does exactly what you are saying. See below.
    Legare Coleman

  13. #13
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    First please let me thank you for being so patient. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    I am so embarrassed. I had to enable macros. I had the security settings to high.
    You have completely understood what I wanted from the beginning. I will make sure I study the VBA extensively!
    You are the total best!
    This is what I needed.
    Thank you again Legare!

  14. #14
    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: Transfer Info to another sheet (Excel 2002 (XP))

    What does the workbook look like after running the macro?

    I tested Legare's code in XL97 and it creates the new worksheets in the workbook with the separate items in them.

    Steve

  15. #15
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Info to another sheet (Excel 2002 (XP))

    It looks perfect in Excel 2002 XP
    You all are so extremely kind! One day soon I will be able to help too.
    Thank you Steve.

Page 1 of 2 12 LastLast

Posting Permissions

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