Excel question

[kabushnell]

I have a worksheet in Excel that contains an ID # and following each ID there is a dollar amount entered each month. Similar to the following:

ID Total Jan Feb Mar
8603 200 125 50 25

There can be the same ID several times. Basically what I have is that no single ID total can be greater than 700, and no ID total can be over 1400. I have formulas written that total each row and then again for the total ID. What I was wondering is if there is a way to have Excel pop up an error when someone enters an amount in one of the months that brings that particular ID over the 700 or 1400 limit?

I hope that isn't to confusing. Right now we have to enter the amounts each month and then go see if anyone has gone over the limits. Any help is greatly appreciated.

 

[RRinTetons]

I don't know how I'd get Excel to pop a warning window, although I'm certain there's a way to do it through VBA. Staying within the standard features of the application I might have an idea.

How many unique ID's are there? Do new unique ID's get added on an ongoing basis?

If there are a manageable number of them and it's a faily stable number maybe you could use Excel's database functions to display a list (second window? split window?) with the totals for each item. Use conditional formatting to make an ID's total display in RED if the total exceeds the allowed values. Maybe use an IF statement to set a flag to one is any single ID's value exceeds the range, check the SUM of the flag fields and set a cell to RED when the SUM of the flag fields is greater than 1.

That's all a bit kludgy, but it could work.....

Richard Ray
Jackson Hole Mtn Resort
Teton Village, WY

 

[TonyJollans]

Hi kabushnell,

It's certainly possible to trigger a warning message box or similar via VBA code and the Worksheet Change Event, but this isn't the VBA forum.

Conditional Formatting can be used to warn the user (by highlighting the line or similar) but not to force them to change anything. If that is sufficient then that's your tool, but if it still leaves you a huge manual checking job you'll probably have to go the code route.

Come back - or post again in the VBA forum - Forum707 - if you want help with code.

Enjoy,
Tony

 

[kabushnell]

Thanks for the responses. I was thinking it might be VBA code that I may have to use. I am trying to avoid manual checking so I will post this again in VBA forum. Thanks for the help.

 

[davidmunoz]

The easiest method would be to include an if statement with sum of the fields your adding up.
example:
if((sum(a1:c1)>699,"Exceeds Limit","&quot

I would put this after the last Monthly column so that as each previous month is entered, the individuals can see the pop up field seeing how it should be the later months that would go over the limit set.

Hope this helps!

 

[bluedragon2]

Do a data validation.

Under menu Data/Validation, do Whole Number 0 for minimum and 700 for maximum. On the tab Error Alert, just enter the message you want to pop up.

Blue

 

[Bowers74]

Tony & Richard,

Check out the thread that kabushnell started in the VBA Forum to check out the solutions given, if you're interested.

Blue,

I suggested the same thing in the VBA Forum thread.

thread707-619576

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[kabushnell]

I did Data Validation but it isn't working quite right. I put the validation in the totals column and when I enter amounts greater than the limits it lets me input them. It only works when I try to manually enter an amount in the totals column greater than the limits.

 

[bluedragon2]

Validation won't pop-up when a formula is used, but if click on tools/auditing/show audit toolbar and click on the second button from the right, circl validation errors, your totals will be circled for each validation error.

Blue

 

[kabushnell]

Very cool. Thanks

 

[Bowers74]

kabushnell,

I think you need to check your post in the VBA Forum. There have been a few suggestions made there too that can help. If you are going to start another post, you should stick to it and not go into the original again.

Go to your post Thread707-619576 to view the other helpful suggestions.

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!