Solved: Excel 2007

[computerman29642]

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?

 

[Yorkshire Guy]

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]

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.

 

[Yorkshire Guy]

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]

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.

 

[Yorkshire Guy]

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]

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.

 

[Yorkshire Guy]

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

 

[computerman29642]

Yorkshire, the attached file looks very impressive.

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?

 

[Yorkshire Guy]

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]

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.

 

[Yorkshire Guy]

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]

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.

 

[Yorkshire Guy]

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

 

[computerman29642]

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.