Copying cell ref from rows to columns

OSUBill

New Member
Joined
Mar 24, 2004
Messages
1
This may be a simple question but I have tried everything I know.

I want to drag a cell reference across a row. The problem is I want to reference cells from another work sheet that is formatted in Columns.

PROBLEM:
I have 12 months across a row. A1:L1
I want to pull in data to these fields from another worksheet but the months are listed in Columns A1:A12
I need to be able to click on A1 and drag formula to L1.

EXAMPLE:
A1 cell ref whould be ('Sheet2'!A1),
B1 cell ref should be ('Sheet2'!A2),
C1 cell ref should be ('Sheet2'!A3).
etc.
I tried putting in the following ('Sheet2'!$A1) but it doesn't drag the cell ref properly.

Thanks for any help.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello and welcome to the board,

You need to use the OFFSET formula.

e.g.

=OFFSET(Sheet6!$A$1,0,ROW(Sheet7!A1)-1)

You will have change Sheet names/cell refs to suit.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

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