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

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
Pathfinder Tags: Excel inventory max / min
Interested in this topic? By joining CR4 you can "subscribe" to
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?

Power-User

Join Date: Apr 2007
Location: Austin, TX
Posts: 367
#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.
Active Contributor

Join Date: Feb 2007
Posts: 12
#3

Re: Excel Macro for Max / Min

01/08/2009 2:16 AM

This is the most simple, usefull solution.

It is sufficient for you ?

Commentator

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

Commentator

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

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

Guru

Join Date: Sep 2006
Posts: 5907
#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

Guru

Join Date: Dec 2007
Location: No. VA, USA (No, it does NOTu mean "won't go"!)
Posts: 1791
#9

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

Join Date: Apr 2008
Posts: 59
#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.

Commentator

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