Pointing to appropriate cells via formula with variables

[roxdrob]

[FONT=&quot]Full Disclosure: I posted a version of this on one of the other Excel Forums last night, but I haven't received an answer. I'm hoping you guys can help[/FONT]
[FONT=&quot]What I'm trying to do is create a product title for many different products based on type, color, and whether it has a charm or connector or both. I did a lot of concatenating to get the results I want. I've probably done everything the hard way, so I thought I would throw this out here and see if anyone could tell me if there is an easier way of doing things. The majority of the work is done in the first tab. The 2nd tab is basically a scratch sheet for me.[/FONT]
[FONT=&quot]I would also like to know: Is it possible to do a vlookup on a table that has a concatenated string and somehow in the formula tell it to change the place holder variable to the correct cell in the main sheet? [/FONT]
[FONT=&quot] Apparently I can't attach anything.[/FONT]
[FONT=&quot]Thanks in advance[/FONT]

 

[roxdrob]

Here is the formula I'm using:

="Women's Handmade "&D2&" Adjustable Stackable Statement Bracelet "&IF(AND(E2<>"",ISBLANK(F2))," with a "&PROPER(E2)&" Charm",IF(AND(ISBLANK(E2),F2<>"")," with a "&PROPER(F2)&" Connector",IF(AND(ISBLANK(F2),ISBLANK(E2)),"",IF(E2<>""&F2<>""," with a "&PROPER(E2)&" Charm and a "&PROPER(F2)&" Connector",""))))

InventoryNr	Color	Charm	Connector	Title/Descripton
ADJ1	Silver		Leaf	Women's Handmade Silver Adjustable Stackable Statement Bracelet with a Leaf Connector
ADJ3	Black & Blue	Handcuffs		Women's Handmade Black & Blue Adjustable Stackable Statement Bracelet with a Handcuffs Charm
ADJ4	White, Blue, Red & Silver			Women's Handmade White, Blue, Red & Silver Adjustable Stackable Statement Bracelet
ADJ5	Blue & Black	Handcuffs		Women's Handmade Blue & Black Adjustable Stackable Statement Bracelet with a Handcuffs Charm
ADJ6	Silver	round "breathe"	filigree	Women's Handmade Silver Adjustable Stackable Statement Bracelet with a Round "Breathe" Charm and a Filigree Connector
ADJ7	Silver		skull	Women's Handmade Silver Adjustable Stackable Statement Bracelet with a Skull Connector
ADJ8	Aqua	"Best Friends" Set		Women's Handmade Aqua Adjustable Stackable Statement Bracelet with a "Best Friends" Set Charm
ADJ9	Silver	Tiny Heart		Women's Handmade Silver Adjustable Stackable Statement Bracelet with a Tiny Heart Charm
ANK2136	Coral			Women's Handmade Coral Beaded Anklet
ANK2137	Black			Women's Handmade Black Beaded Anklet
ANK2138	Blue			Women's Handmade Blue Beaded Anklet

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

	Color	Charm	Connector
	Black	Panda	Love
Type
ADJ	Women's Handmade Love Adjustable Stackable Statement Bracelet
ANK	Dunglebees Women's Handmade Beaded Anklet a Panda Charm and a Love Connector
APB	Dunglebees Women's Handmade Anglican Prayer Beads Episcopal Rosary a Panda Charm and a Love Connector
BAE	Dunglebees Women's Handmade Beaded Bracelet & Earring Set a Panda Charm and a Love Connector
BKM	Dunglebees Women's Handmade Beaded Bookmark a Panda Charm and a Love Connector
BRC	Dunglebees Women's Handmade Black Beaded Stackable Statement Bracelet with a Panda Charm and a Love Connector
CAR	Dunglebees Women's Handmade Beaded Car Charm, Rearview Mirror Hangler, Car Accessory a Panda Charm and a Love Connector
EAR	Dunglebees Women's Handmade Beaded Fishhook Pierced Earrings a Panda Charm and a Love Connector

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Fluff]

Full Disclosure: I posted a version of this on one of the other Excel Forums last night, but I haven't received an answer.

Can you please post a link to your thread on the other Forum as per the rules (see rule 13 here along with the explanation: Forum Rules).

Thanks

 

[roxdrob]

https://www.excelforum.com/excel-fo...ppropriate-cells-via-formula.html#post5169745