Vertical Copy to Horizontal paste

**cbeckwith** · 08-31-2012, 09:40 AM

Below is how the one cell comes in on the remote spreadsheet.

='C:\Users\cbeckwith\Documents\UtilityBill-MasterStorage\Sharepoint-Utilities2012FY(Actuals)\2012FISCAL YEAR COMPLETED\[2012 FY Utilities (UMCB-COMPLETE).xlsx]UMCBrack-CP'!$M5

-CP'!$M5 The column M stays stagnant (Vertical on original), but having a problem in the sheet with the 5 increasing in value in the Horizontal direction, it just stays the same across 12 columns when I drag the link across the columns. Then I have to manually fix all the columns. Is there an easier way to fix this?

**Pauleyb** · 08-31-2012, 09:52 AM

This will require the use of OFFSET. Something like this:
=OFFSET($M5,COLUMN()-COLUMN($O5),0)

Where the $M5 is replaced by your long reference above, and $O5 is replaced with whatever cell this equation is placed in your spreadsheet (it references itself).

**cbeckwith** · 08-31-2012, 10:17 AM

Originally Posted by Pauleyb

This will require the use of OFFSET. Something like this:
=OFFSET($M5,COLUMN()-COLUMN($O5),0)

Where the $M5 is replaced by your long reference above, and $O5 is replaced with whatever cell this equation is placed in your spreadsheet (it references itself).

I appreciate your fast response, but what does the code look like below:
I don't want the column "M" to change, only the row number, 5, then 6, then 7, etc.
Remember, this is a linked spreadsheet

I am going to assume the following
='C:\Users\cbeckwith\Documents\UtilityBill-MasterStorage\Sharepoint-Utilities2012FY(Actuals)\2012FISCAL YEAR COMPLETED\[2012 FY Utilities (UMCB-COMPLETE).xlsx]UMCBrack-CP'!=OFFSET($M5,COLUMN()-COLUMN($O5),12) I believe this is right adding the 12, instead of the 0, because I am wanting twelve columns across, is this right syntax?

**Pauleyb** · 08-31-2012, 10:31 AM

No. Like I said, you need to replace the $M5 in my formula with your long one. I wanted to show you the technique which would be lost with such a long reference.

Let's say you have this in cell A1='C:\Users\cbeckwith\Documents\UtilityBill-MasterStorage\Sharepoint-Utilities2012FY(Actuals)\2012FISCAL YEAR COMPLETED\[2012 FY Utilities (UMCB-COMPLETE).xlsx]UMCBrack-CP'!$M5

Now, copy my equation exactly and paste it into A1. Then change the $M5 in my equation to your long reference (above). Then change the $O5 to $A1. You should be able to drag that horizontally and it will get the data from your reference vertically. Note: I am using A1 in my example now, you will need to change it to whatever cell this equation is in.

**cbeckwith** · 08-31-2012, 11:48 AM

It is failing right where the bold is....I think it is right, but I must be missing something.

=OFFSET('C:\Users\cbeckwith\Documents\UtilityBill-MasterStorage\Sharepoint-Utilities2012FY(Actuals)\2012FISCAL YEAR COMPLETED\[2012 FY Utilities (UMCB-COMPLETE).xlsx]UMCBrack-NT'!$P5,COLUMN()-COLUMN($C14),0)

**Pauleyb** · 08-31-2012, 05:13 PM

What do you mean it is failing where the bold is? Are you getting some sort of error message? If so, what is it? Is the file you are referencing open?