# Dynamic Concatenate based on varying cell address references

1. ## Dynamic Concatenate based on varying cell address references

Hi,

I am looking for a way to concatenate within a dynamic range of cell references without the need for VBA. I’m not sure if this is beyond the realms of Excel formula?

The references will vary depending on the source data. Each cell listed is the start/end cell location address I wish to use. These points are subject to change based on data extracts.
The reference cells will contain individual text/numeric characters I wish to concatenate. The length of concatenate will also vary in length (number of cells.)

A1 is the first start point, B1 is the first end point... then C1-D1 the next indirect range, A2-B2, C2-D2, A3-B3 etc.
____A_____B_____C_____D
1] \$B\$10 \$G\$10 \$P\$10 \$W\$10
2] \$B\$12 \$G\$12 \$P\$12 \$W\$12
3] \$B\$14 \$G\$14 \$P\$14 \$W\$14
4] \$B\$16 \$G\$16 \$P\$16 \$W\$16
5] \$B\$18 \$G\$18 \$P\$18 \$W\$18

I understand concatenate range is not possible for below example, but are there alternate solutions?

=CONCATENATE(INDIRECT(A1):INDIRECT(B1)) – this is what I would like to do.

Which is really =CONCATENATE(B10,C10,D10,E10,F10,G10) or =B10&C10&D10&E10&F10&G10

=CONCATENATE(INDIRECT(C1):INDIRECT(D1))
=CONCATENATE(INDIRECT(A2):INDIRECT(B2)) etc.

There may 100’s of individual lists required of various length which rules out manual interaction.

Another example with different source data, could be as follows: (which rules out a set concatenate formula)

____A_____B_____C____D
1] \$C\$10 \$H\$10 \$K\$10 \$V\$10
2] \$C\$12 \$H\$12 \$K\$12 \$V\$12
3] \$C\$14 \$H\$14 \$K\$14 \$V\$14
4] \$C\$16 \$H\$16 \$K\$16 \$V\$16
5] \$C\$18 \$H\$18 \$K\$18 \$V\$18

Many thanks for any ideas!
hope this makes sense
Sam

2. ## Re: Dynamic Concatenate based on varying cell address references

If I understand what you're wanting to do this is not possible using built-in functions.

You would need a VBA UDF.

There are currently 1 users browsing this thread. (0 members and 1 guests)