Add text to a column based on text or color of another column

%3CLINGO-SUB%20id%3D%22lingo-sub-3528980%22%20slang%3D%22en-US%22%3EAdd%20text%20to%20a%20column%20based%20on%20text%20or%20color%20of%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3528980%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20make%20a%20column%20in%20a%20document%20that%20autofills%20based%20on%20content%20contained%20in%20another%20column.%20Column%20A%20is%20a%20document's%20folder%20path%20so%20it%20has%20a%20lot%20of%20information%20that%20isn't%20useful%20as%20I%20just%20need%20the%20main%20folder%20information.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20if%20Column%20A%20cell%202%20said%20%22company%20name%2Fpolicies%2Fpto%22%20I%20would%20want%20column%20B%20to%20say%20%22policies.%22%20There%20are%204%20potential%20column%20B%20values%20I%20need%20based%20on%20whether%20column%20contains%20a%20certain%20higher%20level%20folder.%20If%20it%20helps%20I%20have%20conditional%20formatted%20Column%20A%20based%20on%20the%204%20values%20to%20each%20be%20different%20colors.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20Flash%20Fill%2C%20but%20it%20got%20very%20confused%20and%20didn't%20work.%20%3DIF%20formulas%20also%20didn't%20work%20for%20me%20because%20each%20folder%20path%20is%20unique%20and%20I%20need%20it%20to%20uses%20contains%20not%20%3D.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20even%20possible%20to%20format%20Column%20B%20the%20way%20I%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3528980%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-3529022%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20text%20to%20a%20column%20based%20on%20text%20or%20color%20of%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3529022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1430178%22%20target%3D%22_blank%22%3E%40Calliea%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3EThere%20are%204%20potential%20column%20B%20values%20I%20need%20based%20on%20whether%20column%20contains%20a%20certain%20higher%20level%20folder.%20If%20it%20helps%20I%20have%20conditional%20formatted%20Column%20A%20based%20on%20the%204%20values%20to%20each%20be%20different%20colors.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIt%20potentially%20helps%20quite%20a%20bit%3A%20I%20wonder%2C%20for%20example%2C%20why%20you%20can't%20use%20whatever%20the%20%22rules%22%20are%20in%20your%20conditional%20formatting%20dialog%20box%20in%20connection%20with%20some%20solution%20--%20an%20IFS%20function%20perhaps%2C%20or%20a%20table%20with%20VLOOKUP%20(or%20INDEX%20and%20MATCH--that%20follows%20the%20same%20logic%20to%20fill%20in%20your%20column%20B.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

Hi there!

 

I am trying to make a column in a document that autofills based on content contained in another column. Column A is a document's folder path so it has a lot of information that isn't useful as I just need the main folder information. 

 

For example if Column A cell 2 said "company name/policies/pto" I would want column B to say "policies." There are 4 potential column B values I need based on whether column contains a certain higher level folder. If it helps I have conditional formatted Column A based on the 4 values to each be different colors.

 

I tried Flash Fill, but it got very confused and didn't work. =IF formulas also didn't work for me because each folder path is unique and I need it to uses contains not =.

 

Is it even possible to format Column B the way I

1 Reply

@Calliea 

You wrote:

There are 4 potential column B values I need based on whether column contains a certain higher level folder. If it helps I have conditional formatted Column A based on the 4 values to each be different colors.

 

It potentially helps quite a bit: I wonder, for example, why you can't use whatever the "rules" are in your conditional formatting dialog box in connection with some solution -- an IFS function perhaps, or a table with VLOOKUP (or INDEX and MATCH--that follows the same logic to fill in your column B.