Help with Excel Macro

Discussion in 'Mac Apps and Mac App Store' started by davidjearly, Mar 6, 2009.

  1. davidjearly macrumors 68020

    davidjearly

    Joined:
    Sep 21, 2006
    Location:
    Glasgow, Scotland
    #1
    Hi guys,

    I'm looking for some help with the above.

    I have an Excel workbook with 5 different sheets. One of the sheets contains a list of customers and there is another sheet where I would like data from the 1st sheet to be copied into.

    e.g. Sheet 1 has the following columns and below these are a list of customers.

    ID
    First Name
    Last Name
    Date Entered
    Order Number
    Date Received
    Date Shipped

    I have another sheet, say Sheet 5 which has some of these columns, and I would like the data to be automatically copied across when entered onto the first sheet.

    Sheet 5 has the following columns:

    ID
    First Name
    Last Name
    Date Entered

    When the ID field is completed in Sheet 1 (this contains a unique alphanumeric ID for each customer), I would like the data for all columns mentioned above in Sheet 5 to be automatically completed with the same values as Sheet 1.

    Can someone help me with a Macro for this task.

    I'm working with Excel 2003 on Windows XP.

    Thanks,
    David
     
  2. MacDawg macrumors P6

    MacDawg

    Joined:
    Mar 20, 2004
    Location:
    "Between the Hedges"
    #2
    Are you sure you want a macro to run?
    If I understand what you are wanting, this sounds like it could be accomplished with vlookups

    Woof, Woof - Dawg [​IMG]
     
  3. davidjearly thread starter macrumors 68020

    davidjearly

    Joined:
    Sep 21, 2006
    Location:
    Glasgow, Scotland
    #3
    I'm not familiar with VLOOKUPS. I'd be happy with whatever solution works TBH!

    So if I wanted to link data entered in cell K5 (Sheet 1) to cell B3 (Sheet 5), and the same for other cells, I can do that with VLOOKUPS?

    Also, is this something that would need to be enabled on every machine that would be used for editing the spreadsheet?

    Many thanks,

    David
     
  4. MacDawg macrumors P6

    MacDawg

    Joined:
    Mar 20, 2004
    Location:
    "Between the Hedges"
    #4
    It is a formula that would be entered in the cells, so nothing needs to be enabled

    Use the Help function and search vlookup and see if gives you the info you need... it is pretty straightforward

    If not, let me know and I'll try to walk you through it and give you a sample

    Woof, Woof - Dawg [​IMG]
     
  5. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #5
    And if you need a formula with more flexibility than VLOOKUP, then consider the combination INDEX/MATCH, which allows lookup in any direction.
     
  6. MacDawg macrumors P6

    MacDawg

    Joined:
    Mar 20, 2004
    Location:
    "Between the Hedges"
    #6
    Is that available in 2003?

    Woof, Woof - Dawg [​IMG]
     
  7. davidjearly thread starter macrumors 68020

    davidjearly

    Joined:
    Sep 21, 2006
    Location:
    Glasgow, Scotland
    #7
    Tried the vlookup function and I understand how it can be used to display a name when certain conditions are met, but I'm not sure how I could get it do what I described in my last post.

    That is to copy values from particular cells in sheet 1 to particular cells in sheet 5.

    Could you maybe provide an example so I can understand if this will do what I need?

    If Sheet 1 contains the ID in A2, how would I use vlookup to automatically put this ID in D5 of Sheet 5?

    From there, I think I could manage.

    Many thanks,
    David
     
  8. Veldek macrumors 68000

    Veldek

    Joined:
    Mar 29, 2003
    Location:
    Germany
    #8
    Why don't you just use the formula

    =IF('Sheet 1'!A2<>"";'Sheet 1'!A2;"")

    in D5 of Sheet 5? Then you can use VLOOKUP to get the corresponding data:

    =VLOOKUP(D5;'Sheet 1'!A1:F65536;x;TRUE)

    where x is the column from which you want the data.
     
  9. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #9
    Yes, it is. I had used it regularly at a Fortune 50 company until I left last year at this time. These examples assume using XL 2000/2002/2003, and possibly 2007, although I have never tried 2007.

    Here is a simple example of the combination formula:

    =INDEX($A$3:$A$8,MATCH(-B3,$B$3:$B$8,0))


    Here is an example of two conditions lookup:

    =INDEX(C1:C12,MATCH(1,(A1:A12="A")*(B1:B12="Type1"),0))

    (which has to be entered with CTRL + SHIFT + ENTER, instead of just ENTER)


    Many other combinations possible.

    .
     
  10. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #10
    Here is an excellent site: Excel User. Be sure to explore everything. I attended one of his [Charley Kyd] training seminars 4 years ago. Best seminar ever!
     

Share This Page