Cell Value when entering another character

[HHR]

Hello again Forum,

I'm still trying to get this xcel sheet to do what I need it to but am having a lot of difficulties.

I'm trying to build a list, which needs a code generated at the end. I'm using these codes in my website later.

I've attached my spreadsheet for your review.

What I'm trying to do is this:

I'll receive a part, I enter that part number into my spreadsheet. From there, I will be placing an x in the cells below to determine if this part is purchased through what particular supplier, what the make and model this part may fit/belong to.

Example:

Part number is entered in cell A3 - row "b" will always be checked off (x) as my website requires a "default" number. From there, I want to categorize this part now. So, I check off C3 cuz this part was purchased from our supplier, the supplier in this example is Lucky7. Now, this part fits the Corvette, so I select the category, Make/Model (cell I3) and cell M3 for the corvette. This part also needs to be categorized by its part - in this case, rear suspension, cell N3/P3.

I need the code to be generated and seperated with commas and a space.

Example for the above: 2,3,9,10, 4,48, 5,32 (2,3,9,10 SPACE 4,48 SPACE, 5,32) For each highlighted green cell (which is a new category, I need a space to seperate them so my website can read the codes).

I hope this isn't too confusing for people to understand but I sorely need help here!!!

[HHR]

Any help would be so greatly appreciated!

[NBVC]

Please wait several hours before bumping... it won't get it answered any sooner. If someone has a response, rest assured you will be responded to... adding unnecessary bumps hinders you because now you have replies registered and many people begin to ignore threads replied to since they assume you have been receiving help.

By the way, where is the 10 coming from in your expected result?

[HHR]

That was not my intent - my eagerness of having help was my intent only, not for "bumping" reasons. My apologies if its coming across as that.

The 10 is a mistake!!! Thanks for pointing that out!! Sorry, i've been scratching my head at this for a few days and thought the good brains in here could help out.

The number should be 2,3,9, SPACE 4,48, SPACE 5,32

[NBVC]

If you can add a row above to flag the green cells (as Excel doesn't recognize colour as a condition and you would need VBA for that.. which is not my strength), then see attached.

A added a popular UDF I like to use to concatenate conditionally,

in the VB editor:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then formula in V4 would be:

=SUBSTITUTE(SUBSTITUTE(TRIM(aconcat(IF(($B$1:$U$1="x")*($B4:$U4="x"),"x"&$B$3:$U$3,IF($B4:$U4="x",$B3:$U3,""))," "))," ",","),"x"," ")

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

[HHR]

Works great for the first row only.

I opened your attached Excel spreadsheet and tried to put a few x's in different parts of the sheet and it didn't work, any further suggestions??

[NBVC]

Sorry, I was a bit careless with the absolute referencing. Try:

=SUBSTITUTE(SUBSTITUTE(TRIM(aconcat(IF(($B$1:$U$1="x")*($B4:$U4="x"),"x"&$B$3:$U$3,IF($B4:$U4="x",$B$3:$U$3,""))," "))," ",","),"x"," ")

confirmed with CTRL+SHIFT+ENTER and copied down.

[HHR]

NBVC, if you were here in front of me i'd kiss ya!!!!
WOO!!! You absolutely rock!

It FINALLY WORKS!!!