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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top