How to Link Cells in Column to Cell in Rows

Posted on

QUESTION :

I have an Excel database. I want to link Column cells to Row Cells. One by one doing this will take so much time. I want to use some formula. Please help. Apart from that I also want to link cells to each other. Means when i click on Cell in Column. It should take me to linked Row Cell. and when i click on same row cell, it should bring me back to same cell in column.

ANSWER :

If we have a column of data, say A1 through A21 and we want to link a row to it, pick a cell and enter:

=INDEX($A$1:$A$11,COLUMNS($A:A),0)

and copy across:

enter image description here

Once this is done, hyperlinks can be assigned either manually or through a VBA macro.

Perhaps the easiest way is this:

  1. Count the number of rows and columns in the Source range.
  2. Select a the Destination range of cells with the opposite number of rows and columns (e.g. if the original range had 3 columns and 2 rows, the target range would have 2 columns and 3 rows).
  3. With the Destination range cells selected, type “=TRANSPOSE(” (use Tab to autocomplete).
  4. Select the source rows
  5. Press Ctrl + Shift + Enter

The animation below demonstrates the process in Excel 2010 with a 1 row x 3 column scenario.

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *