+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting. If cell in column A=x, then any entry in column B must include y

  1. #1
    Registered User
    Join Date
    03-02-2015
    Location
    Louisville, KY
    MS-Off Ver
    2008
    Posts
    8

    Conditional Formatting. If cell in column A=x, then any entry in column B must include y

    Hey there! I work for a small independent retailer and I am making a spreadsheet to catalog new products. The spreadsheet needs to include all pertinent supplier information (i.e. vendor name, UPCs, item numbers, descriptions, size, units, etc). However, the units for instance need to be formatted differently depending on the vendor. I am wondering if there is a way to utilize the conditional formatting feature to ensure that these ranges are formatted correctly.

    For example: If the product we are getting comes from the vendor KeHE, then the unit needs to be expressed as EAx+whatever the order minimum is. So if the order minimum is 1, it would be expressed as EAx1. If the order minimum was 12, it would be expressed as EAx12.

    I want to find a way to ensure that if KeHe is listed as the vendor in column A, then "EAx" is always the prefix of whatever quantity is entered into the unit column. Is that possible?

    Any suggestions would be most welcome!

    Thank you!
    Last edited by rnbblsmm; 03-03-2015 at 12:13 AM.

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

    Re: Conditional Formatting. If cell in column A=x, then any entry in column B must include

    Hi, welcome to the forum

    CF affects the formatting (visual appearance) of a cell, not the cell's contents. 1 possible way around this would be to have the vendor entered in 1 column (A?) and the amount in a 2nd column (B?), and then to have the results shown in a 3td column?.

    You could probably do this wit custom formatting, but only if you had 1 vendor, not multiple vendors
    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

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Conditional Formatting. If cell in column A=x, then any entry in column B must include

    G
    H
    1
    Lookup Table
    2
    Name Prefix
    3
    KeHE EAx
    4
    Def GGA
    5
    6


    A
    B
    C
    1
    Name Qty Expected Result
    2
    KeHE
    2
    EAx2
    3
    Def
    25
    GGA25
    4
    Abc
    65
    65
    5
    0
    6
    0
    7
    0
    8
    0
    9
    0
    10
    0
    11
    0
    12
    0
    13
    0
    14
    0
    15
    0


    A
    B
    C
    1
    Name Qty Expected Result
    2
    KeHE
    2
    =IFERROR(VLOOKUP(A2,G2:H6,2,FALSE)&B2,B2)
    3
    Def
    25
    =IFERROR(VLOOKUP(A3,G3:H7,2,FALSE)&B3,B3)
    4
    Abc
    65
    =IFERROR(VLOOKUP(A4,G4:H8,2,FALSE)&B4,B4)
    5
    =IFERROR(VLOOKUP(A5,G5:H9,2,FALSE)&B5,B5)
    6
    =IFERROR(VLOOKUP(A6,G6:H10,2,FALSE)&B6,B6)
    7
    =IFERROR(VLOOKUP(A7,G7:H11,2,FALSE)&B7,B7)
    8
    =IFERROR(VLOOKUP(A8,G8:H12,2,FALSE)&B8,B8)
    9
    =IFERROR(VLOOKUP(A9,G9:H13,2,FALSE)&B9,B9)
    10
    =IFERROR(VLOOKUP(A10,G10:H14,2,FALSE)&B10,B10)
    11
    =IFERROR(VLOOKUP(A11,G11:H15,2,FALSE)&B11,B11)
    12
    =IFERROR(VLOOKUP(A12,G12:H16,2,FALSE)&B12,B12)
    13
    =IFERROR(VLOOKUP(A13,G13:H17,2,FALSE)&B13,B13)
    14
    =IFERROR(VLOOKUP(A14,G14:H18,2,FALSE)&B14,B14)
    15
    =IFERROR(VLOOKUP(A15,G15:H19,2,FALSE)&B15,B15)
    Attached Files Attached Files

  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/365
    Posts
    52,018

    Re: Conditional Formatting. If cell in column A=x, then any entry in column B must include

    @ Six, pretty much what I was thinking too, nicely put-together sample

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Conditional Formatting. If cell in column A=x, then any entry in column B must include

    Quote Originally Posted by FDibbins View Post
    @ Six, pretty much what I was thinking too, nicely put-together sample
    In fact I thought the same things which you described in post #2 and wanted to convey it but I was not sure whether OP can pick our thoughts since it is quite little bit advanced level suggestions so I made it as an example file for OP's easy understanding

  6. #6
    Registered User
    Join Date
    03-02-2015
    Location
    Louisville, KY
    MS-Off Ver
    2008
    Posts
    8

    Re: Conditional Formatting. If cell in column A=x, then any entry in column B must include

    Thank you! This has been a great help!

+ 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. How to highlight cell on column A if they include any of the strings on column B?
    By sami770 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-22-2013, 09:30 AM
  2. Replies: 5
    Last Post: 09-24-2012, 05:14 PM
  3. Replies: 2
    Last Post: 10-01-2011, 09:45 AM
  4. Replies: 3
    Last Post: 03-21-2006, 05:45 PM
  5. [SOLVED] Conditional formatting row if cell value in certain column is even
    By Hyland Hunt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-02-2005, 05:06 PM

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