Multiple cells reference

E

Elis

Let's suppose the following facts:
I have two cells, each one containing one number.
I want to make a refference of the two values that I need to view in a third
cell, separated by commas or ";" or anything else.
So, if two cells have the "44" and "55" vallues, can I have a third cell
that will contain the following value: "44,55" or "44;55" or "44 55"?
Please help me if there's any chance.

Thanks.
 
T

tim m

If 44 was in A1 and 55 was in B1 you could use:

=A1&","&B1 and it will give you 44,55 (Just change what is between the
quotation marks of you want a semi colon or a space instead of the comma.)
 
E

Elis

Not that... I meant to list the values from multiple cells in a single cell.
Something like =A1&","&A2&","&A3&","&A4&","&A5&","&A6& etc but for a
looooong list of values. So, is there a short mode?
 
G

Gord Dibben

Elis

No shorthand method without some VBA.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ","
'change the comma(",") to your choice of separator
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A20)

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there.

Save the workbook and hit ALT + Q to return to Excel window.

Enter the formula in a helper cell as explained above.


Gord Dibben MS Excel MVP
 
Top