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

1. ## 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!

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

3. ## 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)

4. ## 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. ## Re: Conditional Formatting. If cell in column A=x, then any entry in column B must include

Originally Posted by FDibbins
@ 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. ## 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!

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

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