Help selecting data from another sheet during a Concate formula.

[AirportAndy71]

Have been stuck on this for ages and all the searches I ask do not seem to be able to help.

I am using a CONCATENATE formula to build an exact text string to use in a metadata template for bulk uploading to a database (Aconex)

I have to have Metadata that exactly matches the Aconex database to enable it to load, the text string is built up of partial metadata from cascading data and in the excel workbook i have created cells which have drop-down lists and then also child cascading drop down lists from the initial selection and so on.

In one cell is a drop down selection that i cannot exact the exact metadata i need to complete the CONCATENATE formula but i do have the data in cascading data on another sheet (the one where the drop-down selection is harvested from.

I am trying to get the CONCATENATE formula to look at a result from a drop down selection and then look to another sheet, find that result and then from there look at the column next to it and pull the code that is relevant to that original selection.. ( Im also finding this hard to describe)

I have this formula written up and it completes seven of the eight CONCATONATE but will not produce what i want it too (as described above)

=CONCATENATE(LEFT(Lists!Y2,8)&"-",LEFT(J2,6)&"-",LEFT(I2,2)&"-",LEFT(P2,3)&"-",LEFT(F2,1)&"-",(D2)=IF(COUNTIF(Type!$A$1:$A$54,$D$2)>=ROWS(Type!$A1:$A54),INDEX(Type!$B$1:$B$54,SMALL(IF(Type!$A$1:$A$54=$D$2,ROW(Type!$B1:$B54)),ROW(Type!A1:A54))),"")&"-",LEFT(M2,3)&"-",Q2)

Lists! & Type! are the two other sheets im pulling info for

Cell D2 is a dropdown selection from a list, i need the cell D2 to show the original selection as is, but need the resulting entry in the CONCATENATE Cell A2 to show the text that relates to selection and lives in a sheet (Type!) in a separate table

Heres the data:

[TABLE="width: 442"]
<tbody>[TR]
[TD]DocumentType[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]2D Model[/TD]
[TD]M2[/TD]
[/TR]
[TR]
[TD]3D Model[/TD]
[TD]M3[/TD]
[/TR]
[TR]
[TD]Basis of Design[/TD]
[TD]BD[/TD]
[/TR]
[TR]
[TD]Brief[/TD]
[TD]BR[/TD]
[/TR]
[TR]
[TD]Change Management[/TD]
[TD]CH[/TD]
[/TR]
[TR]
[TD]Commissioning[/TD]
[TD]CM
[/TD]
[/TR]
</tbody>[/TABLE]

So when in the main sheet some one selects 2DModel from the dropdown list i need the CONCATONATE to display the code M2 for example. at the moment im getting 'FALSE' being displayed:

[TABLE="width: 292"]
<tbody>[TR]
[TD="width: 292"]S1000863-35010F-XX-MAC-E-FALSE118-05
[/TD]
[/TR]
</tbody>[/TABLE]
when i need the FALSE to read M2 if 2D Model is selected and so on from the dropdown list.

I hope you can understand this.. and many thanks if anyone can help

 

[steve the fish]

Sure use a vlookup formula within the concat eg CONCATENATE(VLOOKUP("2D Model",myTable,2,0))

 

[AirportAndy71]

=CONCATENATE(LEFT(Lists!Y2,8)&"-",LEFT(J2,6)&"-",LEFT(I2,2)&"-",LEFT(P2,3)&"-",LEFT(F2,1)&"-",(D2)(VLOOKUP("2D Model",TYPE_1,2,0))&"-",LEFT(M2,3)&"-",Q2)

Leaves me with a #REF !

And what if another selection other than '2D Model' is selected from the dropdown pick list in Cell D2?

 

[GR00007]

Would this work?
=LEFT(Lists!Y2,8)&"-"&LEFT(J2,6)&"-"&LEFT(I2,2)&"-"&LEFT(P2,3)&"-"&LEFT(F2,1)&"-"&VLOOKUP($D$2,Type!A:B,2,FALSE)&"-",LEFT(M2,3)&"-",Q2

 

[AirportAndy71]

This gives me the result #NAME ! (I did add the CONCATENATE at the beginning)

 

[GR00007]

Sorry, I missed replacing a comma with an '&' for that formula. This is without the CONCATENATE - '&' does the same thing.
=LEFT(Lists!Y2,8)&"-"&LEFT(J2,6)&"-"&LEFT(I2,2)&"-"&LEFT(P2,3)&"-"&LEFT(F2,1)&"-"&VLOOKUP($D$2,Type!A:B,2,FALSE)&"-"&LEFT(M2,3)&"-",Q2

 

[AirportAndy71]

I removed a comma and added another &, (at the end before the Q2) and it now works, thank you.!

here's the final formula:

=LEFT(Lists!Y2,8)&"-"&LEFT(J2,6)&"-"&LEFT(I2,2)&"-"&LEFT(P2,3)&"-"&LEFT(F2,1)&"-"&VLOOKUP($D$2,Type!A:B,2,FALSE)&"-"&LEFT(M2,3)&"-"&Q2

 

[GR00007]

Oops - I realized that too when setting up a spreadsheet to actually use that formula - I did verify that changing the value in D2 was reflected correctly.
Thankx for the response.