+ Reply to Thread
Results 1 to 8 of 8

Conditional formatting or validation?

  1. #1
    Registered User
    Join Date
    07-10-2018
    Location
    Newbury, England
    MS-Off Ver
    2016
    Posts
    5

    Conditional formatting or validation?

    Hi all
    I'm an IT student working on a data modelling assignment with a scenario that has to do with sales and profit margins.
    This is what I would like to achieve:
    I currently have a table showing the item cost, sale price and profit. My profit margin is separate and when the value is changed it changes only the profit (I don't want it to change the sale price as well). I would like the set up some sort of validation or conditional formatting to ensure that the sale price is always equal to cost plus profit.
    For example (not correct syntax):
    cost price (A1) sales price (A2) profit (A3)
    A2=A1+A3 (true)
    If the statement is not true I would like a message to pop up in another cell (not a pop up box) stating that the user needs to change the profit margin in order to make it true.
    What is the best way to go about doing this sort of thing?
    I hope this makes sense

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,603

    Re: Conditional formatting or validation?

    The thread title is a tad misleading as this is nothing to do with conditional formatting.
    Try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    BSB

  3. #3
    Registered User
    Join Date
    07-10-2018
    Location
    Newbury, England
    MS-Off Ver
    2016
    Posts
    5

    Re: Conditional formatting or validation?

    Quote Originally Posted by BadlySpelledBuoy View Post
    The thread title is a tad misleading as this is nothing to do with conditional formatting.
    Try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    BSB
    Hi there
    Sorry about the title, I wasn't sure what it would fall under.
    Thank you for you advice but nothing shows ups when I use that syntax.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,412

    Re: Conditional formatting or validation?

    Hi, welcome to the forum

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,603

    Re: Conditional formatting or validation?

    Quote Originally Posted by Megabyte35 View Post
    nothing shows ups when I use that syntax.
    The cell will stay empty if A2 does indeed = the sum of A1 & A3. If it doesn't then the message will show.

    But like Ford says, a sample workbook makes the issue in hand far easier for us to comprehend.

    BSB

  6. #6
    Registered User
    Join Date
    07-10-2018
    Location
    Newbury, England
    MS-Off Ver
    2016
    Posts
    5

    Re: Conditional formatting or validation?

    Quote Originally Posted by FDibbins View Post
    Hi, welcome to the forum

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Thank you for the advice, this thread is now solved so I will change it, and I will attach a dummy workbook in the future. I would also like to complement the administrators for running such a helpful forum

  7. #7
    Registered User
    Join Date
    07-10-2018
    Location
    Newbury, England
    MS-Off Ver
    2016
    Posts
    5

    Re: Conditional formatting or validation?

    Quote Originally Posted by BadlySpelledBuoy View Post
    The cell will stay empty if A2 does indeed = the sum of A1 & A3. If it doesn't then the message will show.

    But like Ford says, a sample workbook makes the issue in hand far easier for us to comprehend.

    BSB
    Thank you so much for the help, it is working now .

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,412

    Re: Conditional formatting or validation?

    Quote Originally Posted by Megabyte35 View Post
    ...I would also like to complement the administrators for running such a helpful forum...
    Thank you for the kind words, always appreciated ")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional formatting adn data validation
    By JulijaKT in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-16-2015, 08:12 AM
  2. Conditional Data Validation/Formatting
    By BWellman in forum Excel General
    Replies: 1
    Last Post: 03-30-2014, 02:30 AM
  3. conditional formatting/data validation?
    By 13lack13lade in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2013, 10:47 PM
  4. Data Validation and Conditional Formatting using VBA
    By ABabeNChrist in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-30-2010, 03:53 AM
  5. Conditional Formatting/Data Validation?
    By 1230dc in forum Excel General
    Replies: 2
    Last Post: 10-07-2009, 05:38 AM
  6. Conditional Formatting Validation Lists
    By kingsolo in forum Excel General
    Replies: 2
    Last Post: 01-11-2007, 05:32 AM
  7. Viewing Conditional formatting and/or Validation
    By Juran54 in forum Excel General
    Replies: 12
    Last Post: 03-08-2005, 10:06 PM

Tags for this Thread

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