Need a fast way to combine - pre defined content via ALIASES / IDENTIFIERS.

A1 = "barracks lvl 1"
A2 = "barracks lvl 2"
A3 = "My friend Johny"
A4 = "has to build"

Anywhere i need to type Jo,bld,rax2
and get =A3&A4&A2

Why?
Its easier to remember what i need by identifying it somehow with aliases than remembering each cell address i need. and moving around to find it, and get the address or copy it.

What i can think of is a shitload of nested ifs. But there probably is a simple way of doing this...

Why not use named ranges?

@ Olly
Because i don't know what they are and how to use them (: Wouldn't be asking if i knew (:

@newdoverman
I'll check that out and get back to ya.

Go to the Name Manager, click on new and assign a name. I used ABC. In the formula field enter:

Formula:

=Sheet1!$A$1&Sheet1!$A$2&Sheet1!$A$3&Sheet1!$A$4

Now when you want to use this click in the cell type =ABC enter.

Allrighty i mapped a range from a22 to a32 named ABC.

Any way i can reference the third cell from that range?

like =ABC3 ?

P.S.

Google pointed out to =INDEX(ABC, 3)

But thats kind of bulky, INDEX(ABC,3)&INDEX(ABC,2)&INDEX(JAZ,15)

Try assigning Named Ranges as follows:

A1 = "barracks lvl 1 " - Name as brax1
A2 = "barracks lvl 2 " - Name as brax2
A3 = "My friend Johny " - Name as jo
A4 = "has to build " - Name as bld

Now you just enter:

Formula:

=jo&bld&brax2

It seems that the parameters are changing for this problem.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

How's your VBA?!

Put this code in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, stInput() As String, stOutput() As String
Dim stRangeName As String, iRangeIndex As Integer
Dim i As Integer, j As Integer

On Error GoTo ErrCatch
Application.EnableEvents = False
For Each c In Target
    If c.Value = "" Or c.Column = 1 Then Exit Sub
    stInput() = Split(c.Value, ";")
    ReDim stOutput(UBound(stInput))
    For i = LBound(stInput) To UBound(stInput)
        stRangeName = Left(stInput(i), FirstNumber(stInput(i)))
        iRangeIndex = CInt(Right(stInput(i), Len(stInput(i)) - FirstNumber(stInput(i))))
        stOutput(i) = Range(stRangeName).Cells(iRangeIndex).Value
    Next i
    c.Value = Join(stOutput, "")
Next c
Application.EnableEvents = True
Exit Sub
ErrCatch:
    Debug.Print "Error: " & Err.Number & " - " & Err.Description
    Application.EnableEvents = True
End Sub

Function FirstNumber(ByVal s As String)
Dim i As Integer
For i = 1 To Len(s)
    If IsNumeric(Mid(s, i, 1)) Then
        FirstNumber = i - 1
        Exit Function
    End If
Next i
End Function

Now, just type in the range names / indexes you want, in a cell. No = sign first. For example, enter:

Formula:

PLACES3;MUSIC5;FRUIT2;ALC8;SOFT7

and it will automatically change to:

Formula:

BAR3;JAZ5;APPLE2;VODKA8;TONIC7;

Be aware, it's very rough coding - there's no error handling built in, and it immediately overwrites cell contents on entry, so handle with care! If you think this approach will work, though, then we can work on making it a bit more user friendly and efficient.

@ Olly it crashed.

Anyhow i think its prudent to actually call the function instead of having it check every cell you use or have.

Bernie Deitrick came up with something quite efficient over at the VBA forum:

=StrOffset("namerange 3 namerange 1 namerange 5",1)

Function StrOffset(strAdd As String, iOff As Integer) As String
    Dim V As Variant
    Dim i As Integer
    
    V = Split(strAdd, " ")
    For i = LBound(V) To UBound(V) Step 2
        StrOffset = Trim(StrOffset) & " " & Range(V(i)).Cells(CInt(V(i + 1))).Offset(0, iOff).Value
    Next i
                
End Function

Theres also an option with =SUM but I don't need it.

You can check it out at:
http://www.excelforum.com/excel-prog...th-offset.html

Thank you all. - Solved.