Solved: Excel 2007

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 
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.
 

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.
 
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
 

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.
 

Attachments

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
 

computerman29642

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
How are you updating #Bought?, likely you are going down the column updating that column, yes?
Yes, the number bought is manually entered.

And what happens when a customer gets to 20 bought, does that mean another coupon?
then 30, 40.......
As of right now, the percentage off will be the same. In the future, I may try to do something different.

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.
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.
 
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
 

Attachments

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?
 
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
 

computerman29642

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
Warning: if a customer has previously bought 19, and you update that to 21, it will not give a coupon.
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.
 
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?
 

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. :)
 
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
 

Attachments

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. :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top