Using Wildcard in Cell Reference

[Elfear]

<tbody>

[TD="class: votecell"]

[/TD]
[TD="class: postcell"]I'm building a worksheet that will concatenate cells based off of criteria in another column on the same row. After much searching I found Harlan Groves aconcat UDF and this is the formula I've got so far:

=SUBSTITUTE(aconcat(IF(Labor!$A$8:$A$100=A2," "&Labor!$M$8:$M$100," "))," ","",1)

Labor!A8:A100 looks like this:

• 115012ABCD
• 115013ZYWX
• 115014WASD
• 121018ABCD
• 121018WASD

Cell A2 = 115

Cell B2 has the formula above.

I'm trying to concatenate a summary of all cells that start with the first three digits of the number in Labor!A8:A100. I've got the formula mostly working but my cell reference (A2) is causing me issues. If I use an exact match (e.g. 115012ABCD) in cell A2, my formula works. Otherwise it returns no data.

How can I tell Excel to find values that start with the digits in cell A2 rather than have to be an exact match with cell A2?

[/TD]


</tbody>

 

[JoeMo]

Have you tried substituting
LEFT(Labor!$A$8:$A$100,3) in your formula?

 

[Elfear]

Have you tried substituting
LEFT(Labor!$A$8:$A$100,3) in your formula?

Tried using the Left function and it returned no data. Also tried using 115* in cell A2 but no luck.

 

[Elfear]

Bump.

 

[Marcelo Branco]

Try

LEFT(Labor!$A$8:$A$100,3)=A2&""

M.

 

[Elfear]

Try

LEFT(Labor!$A$8:$A$100,3)=A2&""

M.

Success! Thanks for your input. I've been wrestling with this spreadsheet for the last couple days and that did it.

Probably helps that your from one of my favorite places on earth.

 

[Marcelo Branco]

Success! Thanks for your input. I've been wrestling with this spreadsheet for the last couple days and that did it.

Probably helps that your from one of my favorite places on earth.

You are welcome.. Glad for helping

With the compliments of Rio de Janeiro!

Enjoy
https://www.google.com/search?q=Rio...wKhroG4DQ&ved=0CDsQsAQ&biw=1366&bih=655&dpr=1

M.

 

[Elfear]

You are welcome.. Glad for helping

With the compliments of Rio de Janeiro!

Enjoy
https://www.google.com/search?q=Rio...wKhroG4DQ&ved=0CDsQsAQ&biw=1366&bih=655&dpr=1

M.

Still kicking myself for not visiting Pao de Acucar when I was in Rio. The Cristo statue was awesome though. Pretty weird feeling looking down and seeing clouds.