warehouse, indicate postion in graph

[jaapaap]

Dear All,

I have a map with positions indicated by a GH letter code, Bay number and a Post number.

Besides that I have a list with codes that need to be filled in the MAP. I would like to fill these codes in the map in by a formula but I cannot create the right formula. Can you help me with this.

see example where I filled the map manually.

Regards

[Pete_UK]

You can use this formula in AH4:

=IF(AD4=1,"A",IF(AD4=2,"B",""))&TEXT(AE4,"00")&TEXT(AF4,"00")

to join those codes together. Copy it down where you have data. Then in C4 you can use this formula:

=IFERROR(INDEX($AG:$AG,MATCH($A$3&TEXT($AB4,"00")&TEXT(C$3,"00"),$AH:$AH,0)),"")

Copy across and down to suit your map. Note for the lower map you will need to change the $A$3 (shown in red above) to $A$21, or you could just make it "B".

Note also that you may have to change the commas ( , ) in the formulae to semicolons ( ; ) according to your regional settings.

Hope this helps.

Pete

[jaapaap]

Dear Pete,

It works in the sheet. That was a simplification of the reality. I have added the actual map and I have still 2 things to overcome.

The bay number and on the right I have the other side of the building. Can you create a formula that covers both?

Regards

[jaapaap]

added the wrong sheet, sorry

[Pete_UK]

Put this formula in AC6:

=IFERROR(INDEX($AZ:$AZ,MATCH(TEXT($AA6,"00")&TEXT($AB6,"00")&TEXT(AC$5,"00"),$BA:$BA,0)),"")

then copy across and down to suit the plan on the right hand side.

Hope this helps.

Pete

[jaapaap]

Dear Pete,

This is what I needed. Thanks for the solution!!