+ Reply to Thread
Results 1 to 2 of 2

Dynamic Concatenate based on varying cell address references

  1. #1
    Registered User
    Join Date
    12-02-2014
    Location
    Bristol
    MS-Off Ver
    2003/2007
    Posts
    9

    Question 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. Im 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.

    Can you advise on how to limit the return to the start and end point?

    There may 100s 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
    Attached Files Attached Files

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Dynamic Concatenate based on varying cell address references

    I didn't download your file.

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

    You would need a VBA UDF.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 04-12-2015, 10:33 AM
  2. [SOLVED] Varying validation drop-downs based on varying cell values
    By navarreman1 in forum Excel General
    Replies: 2
    Last Post: 08-08-2013, 08:13 AM
  3. Concatenate address lines of varying number
    By tone640 in forum Excel General
    Replies: 4
    Last Post: 08-08-2011, 08:18 AM
  4. Can't concatenate a cell address
    By JohnSidney in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-12-2010, 10:58 AM
  5. Divide by a value in a varying cell address
    By cottage6 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1