1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007

Discussion in 'Business Applications' started by computerman29642, Sep 30, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I am planning to create an Excel workbook that will consist of: Customer Names and Number of Items Bought.

    What I would like to do if a customer has bought ten items, I would like to create a coupon for a certain percentage off.

    For Example, if the customer buys 10 items, then they will get a 20% off coupon.

    Does anyone know how I can create the coupon using the information in Excel?
     
  2. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    How are you wanting to control this?
    - automation as soon as the number of items bought reaches 10
    - manually decide to create a coupon yourself

    Simplist would be to run a Word Mail Merge periodically selecting on no of items bought >= 10 to print the coupons.
    But you will need another flag to indicate 'coupon already printed', which you would need to filter out of the mail merge also.

    HTH
    Hew

    PS I said >= 10 in the above in case say they have just gone from previously 9 to now 11 items bought scenario.
     
  3. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    What I was thinking that when the number reaches 10 or greater, then a hyperlink to the coupon would appear for me to click on.
     
  4. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    somehow I thought you were going to say that!

    unfortunately, I'm about to log off, but I'm thinking that a macro to:
    determine that a cell change has occured in the column 'no of items bought',
    check that a coupon has not already been printed,
    check that the number of items bought is >= 10,
    create the coupon on another sheet using the customer details from the main sheet,
    print the coupon,
    return to the main sheet and set the 'coupon alreday printed flag'

    if I get chance I'll have a play later, or maybe you could try this one yourself!, or just do manually.

    lol
    Hew
     
  5. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Please see the atatched workbook. I know the coupon will most likely end up on a hidden worksheet, but here is how it all look.

    On the coupon, the bold words "Person's Name" is where I would like Excel to automatically insert the customers name. The coupon field is where I was thinking the hyperlink would go.
     

    Attached Files:

  6. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    Let's get a few more details of how you want this to work.

    How are you updating #Bought?, likely you are going down the column updating that column, yes?

    And what happens when a customer gets to 20 bought, does that mean another coupon?
    then 30, 40.......

    Your Coupon is a drawing, it would be better to have that on another sheet with at least a cell to have the Customer Name in it.

    Hew
     
  7. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Yes, the number bought is manually entered.

    As of right now, the percentage off will be the same. In the future, I may try to do something different.

    I agree that the coupon should be on a different sheet.

    If you like, I can move the coupon to a different sheet and re-attach the workbook. Just let me know.
     
  8. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    OK computerman29642, see if the attached is what you want.

    Notes:
    I have renamed Sheet1 as "Orders", and added a new Sheet "Coupon", the latter will be the latest Coupon printed.

    Change "Coupon" as you like noting that the Customer Name goes in Cell A7.

    On "Orders" there is a hidden column C for 'Previous Coupon At' - this holds the #bought value at which a previous Coupon was printed; to be eligible for a coupon the #Bought must be a multiple of 10 and >= the Previous Coupon At value.
    Do not insert any columns between B & C.

    Warning: if a customer has previously bought 19, and you update that to 21, it will not give a coupon.

    The macro is in "Orders".
    lol
    Hew
     

    Attached Files:

  9. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Yorkshire, the attached file looks very impressive. :D (y)

    Is there not a way to create a hyperlink to the coupon when one is eligible? The way you have it setup now, what happens if the user clicks "No" on printing the coupon then?

    Why am I unable to get Test User8 to print a coupon?
     
  10. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    Hi again,

    I'm not sure what you mean by a hyperlink, that's just a 'jump' to somewhere,; where do you want to go?

    If you answer 'NO' to 'Print a Coupon', then it wont ask you again until #Bought is a multiple of 10 once more.

    TestUser8 works fine for me, I changed the #Bought to 10, or again at 20...30....
    But not if you've printed a coupon at say 20 and then changed the #Bought back to 10.

    lol
    Hew
     
  11. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Is there not a way to fix this? If the customer previously bought 19, and then bought two more items, then they should get a coupon.
     
  12. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    That's why I put in the warning, I was keeping it simple: Coupons at multiples of 10 bought.

    What do you want if the customer goes from 1 to say 21 #Bought, do they get 2 coupons?
     
  13. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    No, they should only get one coupon.

    Would you make any other suggestions? Could you break down the code for me? I always like to be sure I know what is taken place.

    I really appreciate the help. You have been great. :)
     
  14. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    Hi computerman29642,

    Attached is a new version of the spreedsheet with changed logic, I've also unhidden Col C so you can see it's new usage.

    The logic is now this:

    Cocept:- The no of coupons a customer is eligible for is the #bought / 10.
    So, if we know that value the previous time a coupon was awaded (col C), and now that value is higher, then a new coupon may be printed.

    If you choose not to print a coupon this time, then it will offer you the option again next time.
    (So you could for example, skip printing a coupon at say #bought =10, or 11, and wait 'till you get to 12; but the next would still be at 20).

    If you reduce the #bought, then you won't be offered a coupon again until you get to the the next coupon point beyond the original (so if you award a coupon at #bought = 10, but then get a customer return and reduce the #bought to 9, it won't offer a coupon again until 20 - the customer has already had the coupon at #bought = 10).

    I think that should do you nicely.

    lol
    Hew
     

    Attached Files:

  15. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Hew, I believe you have done it. I will play around with it more to be sure. :)

    Thank you so much for all your help. You have been great. :)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/754822

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice