I have a row of cells with brand names. I want to copy the contents of
the row of cells to a column on another sheet. How can I achieve this
so that whenever I change a cell value on the row I also modify the
value of the cells in the column in question? I was shown how to do
this once, a long time ago, but I have forgotten. I think it involved
the Vlookup function but I am not positive about it.

Thanks...Carlos

In EXCEL 2007 take the following action:-

1. Enter the following information in cells G9 to G12 (of Sheet1):-

Persil
Tesco
Woolworths
Coca Cola

These are the brand names to which you refer.

You can enter this information in any cells you like but I have chosen those
mentioned above.

2. Highlight the range of cells into which you have entered the information
(I chose cells G9 to G12).

3. Do a Ctrl-C to copy the data.

4. Click on Sheet2.

The cursor will default to cell A1.

5. Home / Paste / Paste Link.

Cells A1 to A4 inclusive of Sheet2 will now contain the following data:-

Persil
Tesco
Woolworths
Coca Cola

6. Now whenever you change the information entered at number 1. above the
data in Sheet2 will change automatically.

Please hit Yes if my comments have helped.

Thanks!






"CF" wrote:

I have a row of cells with brand names. I want to copy the contents of
the row of cells to a column on another sheet. How can I achieve this
so that whenever I change a cell value on the row I also modify the
value of the cells in the column in question? I was shown how to do
this once, a long time ago, but I have forgotten. I think it involved
the Vlookup function but I am not positive about it.

Thanks...Carlos
.

To link and Transpose from row to column.

Assumes sheet1 data is in A1:G1

In A1 of sheet2 enter =INDEX(Sheet1!$1:$1,ROWS($1:1))

Drag/copy down to A7


Gord Dibben MS Excel MVP



On Tue, 15 Dec 2009 20:35:01 -0800 (PST), CF wrote:

I have a row of cells with brand names. I want to copy the contents of
the row of cells to a column on another sheet. How can I achieve this
so that whenever I change a cell value on the row I also modify the
value of the cells in the column in question? I was shown how to do
this once, a long time ago, but I have forgotten. I think it involved
the Vlookup function but I am not positive about it.

Thanks...Carlos

On Dec 16, 11:12*am, Gord Dibben <gorddibbATshawDOTca wrote:
To link and Transpose from row to column.

Assumes sheet1 data is in A1:G1

In A1 of sheet2 enter *=INDEX(Sheet1!$1:$1,ROWS($1:1))

Drag/copy down to A7

Gord Dibben *MS Excel MVP

On Tue, 15 Dec 2009 20:35:01 -0800 (PST), CF wrote:
I have a row of cells with brand names. I want to copy the contents of
the row of cells to a column on another sheet. How can I achieve this
so that whenever I change a cell value on the row I also modify the
value of the cells in the column in question? I was shown how to do
this once, a long time ago, but I have forgotten. I think it involved
the Vlookup function but I am not positive about it.

Thanks...Carlos

Thanks Gord. That helped a lot. What about the inverse? I have a
column of names and want to transpose it to a row? I tried the Index
function with Columns() but I haven't figured out how this thing works
yet. A bit more help would be appreciated.

Thanks again...Carlos

I don't know how INDEX works for column to rows either<g

I use OFFSET or INDIRECT

=OFFSET(Sheet1!$C$1,COLUMN(A1)-1,)

entered in A1 of Sheet2 and copied across will pull column C from Sheet1.

=INDIRECT("Sheet1!C"&COLUMN(A1)) same as OFFSET above.



Gord

On Thu, 17 Dec 2009 20:54:42 -0800 (PST), CF wrote:

On Dec 16, 11:12*am, Gord Dibben <gorddibbATshawDOTca wrote:
To link and Transpose from row to column.

Assumes sheet1 data is in A1:G1

In A1 of sheet2 enter *=INDEX(Sheet1!$1:$1,ROWS($1:1))

Drag/copy down to A7

Gord Dibben *MS Excel MVP

On Tue, 15 Dec 2009 20:35:01 -0800 (PST), CF wrote:
I have a row of cells with brand names. I want to copy the contents of
the row of cells to a column on another sheet. How can I achieve this
so that whenever I change a cell value on the row I also modify the
value of the cells in the column in question? I was shown how to do
this once, a long time ago, but I have forgotten. I think it involved
the Vlookup function but I am not positive about it.

Thanks...Carlos

Thanks Gord. That helped a lot. What about the inverse? I have a
column of names and want to transpose it to a row? I tried the Index
function with Columns() but I haven't figured out how this thing works
yet. A bit more help would be appreciated.

Thanks again...Carlos