1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Ignoring Blank cells in an Excel 2003 Formula

Discussion in 'Business Applications' started by cumbaa, Sep 24, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. cumbaa

    cumbaa Thread Starter

    Joined:
    Sep 24, 2009
    Messages:
    4
    Ok, here's the problem.
    I have a very large spreadsheet with catalog prices by location (we have four locations). What I need is to verify which prices are different by location.
    I have each location in a different column and want to do a true false statement to check and make sure the prices are the same at each location. The problem is that we don't always grow the same plant at all lcoations so on cell may be blank. I can't figure out what formula I should use. Please help!
     
  2. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    If for eaxample a cell is blank, but the other 3 locations do have a price, what do you want the result to be?

    I assume that you have a seperate column for each location as well as a seperate column for each price for each location. So lets say 8 columns.

    What is the exact layout?
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    The easiest way to do this is by using a user defined function using VBA. Can you post a sample workbook showing your layout and I'll explain how to do this. I assume the plant name is in Column A and each location price is in the adjacent columns?

    Regards,
    Rollin
     
  4. cumbaa

    cumbaa Thread Starter

    Joined:
    Sep 24, 2009
    Messages:
    4
    I want it to return a value of true if the prices match and false if the prices do not match. I have attached a sample spreadsheet....
     

    Attached Files:

  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    See attached example. If you need a detailed explanation just let me know. My function assumes that column G is used to display the result.

    Regards,
    Rollin
     

    Attached Files:

  6. cumbaa

    cumbaa Thread Starter

    Joined:
    Sep 24, 2009
    Messages:
    4
    need a little more help. The spreadsheet I attached was very basic information. The spreadsheet I am working on looks like the new attachment. When I tried to amend the vbscript I now get a #name? error. I need the answer in H. Please help...
     

    Attached Files:

  7. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    take a look at the attached spreadsheet
     

    Attached Files:

  8. cumbaa

    cumbaa Thread Starter

    Joined:
    Sep 24, 2009
    Messages:
    4
    works beautifully! Thank you so much.
     
  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Nice Work AJ,

    I completely forgot about using the MAX and MIN functions instead of writing a user defined function.

    Regards,
    Rollin
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/863543

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice