Solved: Excel 2007

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.

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

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

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

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