Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2999030%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2999030%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20having%20difficulties%20figuring%20out%20a%20formula.%26nbsp%3B%20I%20thought%20I'd%20figured%20it%20out%20but%20then%20it%20didn't%20work%20and%20so%20I%20am%20at%20a%20loss.%26nbsp%3B%20I've%20attached%20a%20modified%20copy%20of%20the%20spreadsheet%20I've%20been%20using.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'm%20seeking%20on%20the%20%22Vendor%20Sheet%22%20tab%3A%3C%2FP%3E%3CUL%3E%3CLI%3EI%20want%20to%20create%20a%20formula%20where%20if%20Cell%20E2%20is%20Blank%2C%20it%20displays%20red%20like%20it%20currently%20does%3C%2FLI%3E%3CLI%3EThe%20formula%20in%20Cell%20E2%20also%20needs%20to%20check%20the%20%22Values%22%20tab%2C%20and%20if%20C2%20%3D%20Green%2C%20it'll%20display%20Walmart%20in%20Cell%20E2%20on%20the%20%22Vendor%20Sheet%22%20tab%3C%2FLI%3E%3CLI%3EThe%20formula%20in%20Cell%20E2%20also%20needs%20to%20check%20the%20%22Values%22%20tab%2C%20and%20If%20D2%20%3D%20Green%2C%20it'll%20display%20Safeway%20in%20Cell%20E2%20on%20the%20%22Vendor%20Sheet%22%20tab%3C%2FLI%3E%3C%2FUL%3E%3CP%3EMaybe%20there%20is%20an%20easier%20way%20to%20accomplish%20what%20I'm%20attempting%20too.%26nbsp%3B%20Basically%20if%20on%20the%20%22Vendor%20Sheet%22%20tab%2C%20Cell%20B5%20is%20blank%2C%20I%20want%20cell%20E2%20to%20be%20Red.%26nbsp%3B%20If%20Cell%20B5%20has%201-6%20characters%2C%20I%20want%20E2%20to%20display%20%22Walmart%22.%26nbsp%3B%20If%20it's%207-8%20characters%2C%20I%20want%20E2%20to%20display%20%22Safeway%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated.%26nbsp%3B%20I%20thought%20I'd%20figured%20it%20out%20but%20my%20expertise%20in%20Excel%20is%20limited%20to%20what%20I%20learned%20in%20High%20School%20back%20in%202005.%26nbsp%3B%20It's%20driving%20me%20crazy%20being%20unable%20to%20reconcile%20the%20formula%20like%20I%20thought%20I%20could.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2999030%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2999104%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2999104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1227877%22%20target%3D%22_blank%22%3E%40KevinCommunitas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(LEN(B5)%26gt%3B%3D1%2CLEN(B5)%26lt%3B%3D6)%2C%22Walmart%22%2CIF(AND(LEN(B5)%26gt%3B%3D6%2CLEN(B5)%26lt%3B%3D8)%2C%22Safeway%22%2C%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20the%20formula%20you%20are%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I'm having difficulties figuring out a formula.  I thought I'd figured it out but then it didn't work and so I am at a loss.  I've attached a modified copy of the spreadsheet I've been using.

 

What I'm seeking on the "Vendor Sheet" tab:

  • I want to create a formula where if Cell E2 is Blank, it displays red like it currently does
  • The formula in Cell E2 also needs to check the "Values" tab, and if C2 = Green, it'll display Walmart in Cell E2 on the "Vendor Sheet" tab
  • The formula in Cell E2 also needs to check the "Values" tab, and If D2 = Green, it'll display Safeway in Cell E2 on the "Vendor Sheet" tab

Maybe there is an easier way to accomplish what I'm attempting too.  Basically if on the "Vendor Sheet" tab, Cell B5 is blank, I want cell E2 to be Red.  If Cell B5 has 1-6 characters, I want E2 to display "Walmart".  If it's 7-8 characters, I want E2 to display "Safeway".

 

Any help is greatly appreciated.  I thought I'd figured it out but my expertise in Excel is limited to what I learned in High School back in 2005.  It's driving me crazy being unable to reconcile the formula like I thought I could.

2 Replies

@KevinCommunitas 

=IF(AND(LEN(B5)>=1,LEN(B5)<=6),"Walmart",IF(AND(LEN(B5)>=7,LEN(B5)<=8),"Safeway",""))

 

Is this the formula you are looking for?

@KevinCommunitas 

Formula in E2:

 

=IFS(B5="","",LEN(B5)<=6,"Walmart",LEN(B5)>6,"Safeway")

 

Change the formula in the conditional formatting rule for E2 to

 

=E2=""