+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Excel 2007 Compare 2 Columns for inventory reorder levels

  1. #1
    Registered User
    Join Date
    04-04-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Excel 2007 Compare 2 Columns for inventory reorder levels

    This is killing me. I've read and read and read. I can't figure this one out. I konw it is super simple.

    I have 2 columns. "RE ORDER LEVEL" and "IN STOCK" I want the re order level to turn red when the in stock goes below the re order level. I can make the formula for 1 cel but I can't seem to specify a range. Every time I try to specify the range it says I can't and I should use something like =SUM I don't want a sum I want it to compare the in stock quantity with the reorder level and if instock is less than reorder turn re order red but I need to apply the same formula for 300 rows quickly. Can someone tell me where I'm going wrong here??? I'm sure this is a simple formula.


    THANKS SOOO MUCH!
    Last edited by jeremylegasse; 04-04-2011 at 11:33 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Excel 2007 Compare 2 Columns for inventory reorder levels

    Hi jeremy and welcome to the forum,

    These Conditional Formatting equations are mostly trial and error for me. Find the attached with the answer I think you want.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    04-04-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Excel 2007 Compare 2 Columns for inventory reorder levels

    I don't understand what is going on there... Every time I punch in a number in the "IN STOCK" the reorder quantity column shifts all up. It doesn't make any sense to me. I would imagine that the row would not move if any value is changed. Just the formatting of the cell in the "re order" column. But each re order cell needs to react based off the in stock quantity in that row.

    Did you mess up the formula you were trying to show me or am I just not understanding this?

    Thanks for your help and quick response!

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Excel 2007 Compare 2 Columns for inventory reorder levels

    Hi jeremy,

    I work a lot better with a sample file created by you. A lot of people don't supply a sample so I make one myself. I like to made a good sample and frequently use the RandBetween function to generate the numbers.

    I used RandBetween to create the two columns of numbers. They recalculate at each change in the sheet. I believe this is what is going on. If you press F9 a few times to recalculate the sheet the numbers will be randomly recreated and the conditional formatting formula can be confirmed.

    Sorry that I confused you.

    The Conditional Formating formula is correct and you should examine it to make yours correct.

  5. #5
    Registered User
    Join Date
    04-04-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Excel 2007 Compare 2 Columns for inventory reorder levels

    Yep.. That was exactly it! Thank you so much! I knew it was simple. I just recreated my own data and applied the formula to those columns and it worked perfectly. It's wierd how you specify <A2 and apply it to the other column or cells. I would just assume you need to say something <A2:A300

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1