Indirect Address Function

johnstewartjr

New Member
Joined
Dec 10, 2009
Messages
16
Dear Mr. Excel,

I need to copy values that will be updated from "Sheet1" and transpose these values in "Sheet2". CopyPaste Transpose will not work because the values in "Sheet1" are based on formulas that will be updated frequently. Will Indirect Address work, and if so what is the best method?
Thanks
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
Welcome to the board!

Can you please clarify your question? Are you trying to copy and paste *values* or links to other cells?

It sounds like you are maybe trying to achieve something like this:
Excel Workbook
ABCDE
1VALUES TO COPY (RESULT OF COMPLEX FORMULAS)
212345
Sheet1
Excel 2003

Excel Workbook
A
1COPIED VALUES
21
32
43
54
65
Sheet2
Excel 2003
Cell Formulas
RangeFormula
A2=Sheet1!A2
A3=Sheet1!B2
A4=Sheet1!C2
A5=Sheet1!D2
A6=Sheet1!E2
 
Last edited:

johnstewartjr

New Member
Joined
Dec 10, 2009
Messages
16
Sorry about that, I am trying to copy and paste links from one sheet to another (rows to columns).
Thanks
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
So it is exactly like the picture I posted, only more cells involved? You want cells in Sheet2 to be equal to cells in Sheet1?
 

johnstewartjr

New Member
Joined
Dec 10, 2009
Messages
16

ADVERTISEMENT

It is a long string of data so will require a drag down of links to cells from rows in Sheet1 to columns in Sheet2, so I don't think it's as simple as just linking the cells.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
OK, here's how you do it.

Make a mental not of the size and shape of the range you want copied...Say for example 10 columns by 20 rows

On the other sheet,
Highlight a range that is the opposite size and shape (20 columns by 10 rows per example above).
Type
=TRANSPOSE(originalrange) <---DO NOT PRESS ENTER
Press CTRL + SHIFT + ENTER
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
Another way that you can do it is to set the cells in Sheet2 equal to the ones in Sheet1 *without transposing them* for example in Sheet2, A1 would be

=Sheet1!A1

Then, select the cells on Sheet2 and make all of the references absolute with the code from post #6 of this thread: http://www.mrexcel.com/forum/showthread.php?t=408202&highlight=absolute+cells

Then you can copy and paste special->transpose the absolute references that you have now created on Sheet2. Maybe a little more convoluted, but the end result is probably easier to work with...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top