Previous in Forum: HDD Partition and CD Drive - Drive Letter Problem   Next in Forum: Window error
Close
Close
Close
11 comments
Commentator
Engineering Fields - Manufacturing Engineering - New Member United States - Member - New Member Popular Science - Cosmology - New Member

Join Date: Aug 2008
Location: Indiana / Ohio
Posts: 56

Excel Macro for Max / Min

01/07/2009 9:18 AM

I am setting up an inventory spread sheet in excel and want to initiate flags form (max / mins) Is there a formula or macro that you would work for achieving this.

I already have the current inventory column as a formula which is the (beginning balance plus (+) Produced minus (-) shipped)

__________________
Regards, and stay Positive
Register to Reply
Pathfinder Tags: Excel inventory max / min
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.
Anonymous Poster
#1

Re: Excel Macro for Max / Min

01/07/2009 10:57 PM

How about using conditional formatting to make the cells values change to different colors when they exceed the min/max?

Register to Reply
Power-User
United States - Member - New Member Engineering Fields - Chemical Engineering - New Member

Join Date: Apr 2007
Location: Austin, TX
Posts: 367
Good Answers: 10
#2

Re: Excel Macro for Max / Min

01/07/2009 11:57 PM

How about a formula like this IF(A1=MAX($A$1:$A$30),"WHOABUDDY","LITTLEFISH") in the adjacent column? The min would be similar. Or you could put them together in an OR statement in the IF statement.

__________________
Money doesn't talk, it screams in your face.
Register to Reply
Active Contributor

Join Date: Feb 2007
Posts: 12
#3
In reply to #2

Re: Excel Macro for Max / Min

01/08/2009 2:16 AM

This is the most simple, usefull solution.

It is sufficient for you ?

Register to Reply
Commentator
Engineering Fields - Manufacturing Engineering - New Member United States - Member - New Member Popular Science - Cosmology - New Member

Join Date: Aug 2008
Location: Indiana / Ohio
Posts: 56
#4

Re: Excel Macro for Max / Min

01/08/2009 9:37 AM

thanks the conditional formating will work just fine. I keep learning and am thankful that I can

Best regards

RW

__________________
Regards, and stay Positive
Register to Reply
Anonymous Poster
#5

Re: Excel Macro for Max / Min

01/08/2009 10:02 AM

rwindbigler, you could try this.

Somewhere on you spreadsheet place the values for MIN QTY and MAX QTY in separate cells, lets say A1 and A2 respectively.

Say you have your running total in column D.

Say you last total is in cell D31. In column E, next to your last total enter the following formula:

IF(D31<=A1,"PRODUCE MORE",IF(D31>=A2,"STOP PRODUCTION","INVENTORY OK"))

Then as you enter + and - you can drag the formula in E down.

Register to Reply
Commentator
Engineering Fields - Manufacturing Engineering - New Member United States - Member - New Member Popular Science - Cosmology - New Member

Join Date: Aug 2008
Location: Indiana / Ohio
Posts: 56
#6
In reply to #5

Re: Excel Macro for Max / Min

01/08/2009 10:06 AM

Thank you I will work with that formula, it sounds like a good one.

RW

__________________
Regards, and stay Positive
Register to Reply
Anonymous Poster
#7

Re: Excel Macro for Max / Min

01/08/2009 10:49 AM

rwindbigler, one more item you need in your formula, for cell references to your MIN and MAX, the A1 and A2, you will need to lock the cell reference or it will roll up when you drag the formula down. So they should be $A$1 and $A$2

Grimale ( waiting for e-mail to complete registration on CR4 )

Register to Reply
Guru
Engineering Fields - Electromechanical Engineering - Technical Services Manager Canada - Member - Army brat Popular Science - Cosmology - What is Time and what is Energy? Technical Fields - Architecture - Draftsperson Hobbies - RC Aircraft - New Member

Join Date: Sep 2006
Location: Clive, Alberta, Canada
Posts: 5907
Good Answers: 204
#8

Re: Excel Macro for Max / Min

01/08/2009 10:57 AM

I would put a button on the sheet, and run a vba macro that loops through the inventory list, and generates a report (copied to a separate sheet) that can be printed and used to correct the issues listed.

if interested, let me know, I'll show you how to code it.

chris

Register to Reply
Guru
Popular Science - Weaponology - New Member Engineering Fields - Engineering Physics - New Member Engineering Fields - Nuclear Engineering - New Member

Join Date: Dec 2007
Location: No. VA, USA (No, it does NOTu mean "won't go"!)
Posts: 1791
Good Answers: 75
#9
In reply to #8

Re: Excel Macro for Max / Min

01/08/2009 12:08 PM

Chris:

I've been following this thread from a "I might be able to use that sometime later" standpoint, but I may have missed something. Are the errors you would report of the "wrong data" variety, or are they of the "this step has an (or this specific, even) error"?

Either would be useful to me, and I would like any help you could offer, but a program checker would be of more value.

Micah

__________________
Been away a while. Miss all my old friends. Some of you I KNOW are still around. Where are the rest?
Register to Reply
Commentator

Join Date: Apr 2008
Posts: 59
Good Answers: 2
#10

Re: Excel Macro for Max / Min

01/08/2009 12:09 PM

Read up on CONDITIONAL FORMATTING, this will achieve what you want.

There are some nice automatic condtional formats in excel 2007, you can actually put micro-bar-graphs in the cells showing their rank, or colored dots for min and max values of the selected range etc etc etc.

Register to Reply
Commentator
Engineering Fields - Manufacturing Engineering - New Member United States - Member - New Member Popular Science - Cosmology - New Member

Join Date: Aug 2008
Location: Indiana / Ohio
Posts: 56
#11

Re: Excel Macro for Max / Min

03/01/2011 4:01 PM

Very good ideas and suggestions it has helped.

__________________
Regards, and stay Positive
Register to Reply
Register to Reply 11 comments
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.
Copy to Clipboard

Users who posted comments:

Anonymous Poster (3); betomachine (1); chrisg288 (1); Critcho (1); ICPEAR (1); micahd02 (1); rwindbigler (3)

Previous in Forum: HDD Partition and CD Drive - Drive Letter Problem   Next in Forum: Window error

Advertisement