auto fill so A1:A30 becomes A1:AD!

D

duvius

New Member
Joined
Apr 19, 2013
Messages
10
I am trying to enter a cell reference in Sheet1, cell A1 that says =sheet2!A1. When I drag across and autofill I want sheet1!B2 to equal sheet2!A2. I tried transpose but it copies values. I need the cell reference too. Not just the value.

Thanks!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
IF you drag across from A1 it should reference Sheet2!B1 !!
The simply drag the reference in B1 down as far as required....??
 
Upvote 0
Try this:
Code: 
=OFFSET(Sheet2!$A$1,0,ROW(A1)-1)

Edit: Was confused which direction you were going.
 
Last edited:
Upvote 0
[TABLE="width: 108"]
<!--StartFragment--> <colgroup><col width="54" span="2" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"]F[/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"]G[/TD]
[/TR]
[TR]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"]22[/TD]
[TD="class: xl74, width: 54, align: right"]$38,300 [/TD]
[TD="class: xl74, width: 54, align: right"][/TD]
[TD="class: xl74, width: 54, align: right"]$2,935 [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]23[/TD]
[TD="class: xl75, align: right"] 35,365 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 3,174 [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]24[/TD]
[TD="class: xl75, align: right"] 32,192 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 3,432 [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]25[/TD]
[TD="class: xl75, align: right"] 28,760 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 3,711 [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]26[/TD]
[TD="class: xl75, align: right"] 25,049 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 4,013 [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]27[/TD]
[TD="class: xl75, align: right"] 21,036 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 4,340 [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]28[/TD]
[TD="class: xl75, align: right"] 16,696 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 4,693 [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]29[/TD]
[TD="class: xl75, align: right"] 12,003 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 5,075 [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]30[/TD]
[TD="class: xl75, align: right"] 6,928 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 5,488 [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]31[/TD]
[TD="class: xl75, align: right"] 1,440 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 1,440 [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]32[/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]33[/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] 0 [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"] - [/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]
In sheet 2 in cell F238 I have ='Sheet1'!F22+'Sheeet1'!G22. Now when i drag across to G238 I want ='Sheet1'!F23+'Sheeet1'!G23
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Autofill from another workbook - fill column with row data.
Replies
3
Views
510
Fluff
Fluff
G
  • Question Question
Weird drag-and-drop behavior
Replies
1
Views
298
Fluff
Fluff
L
  • Locked
  • Question Question
*Updated Question*...on creating a Bi-directional (two-way) Link in Excel that can link multiple rows and columns?? (You all are amazing!)
Replies
2
Views
310
Joe4
Joe4
U
  • Question Question
When a value is detected in a cell, a VBA code or function will insert a new row with specific values and functions already in each cell
Replies
0
Views
315
ubergreen
U
L
  • Solved
Cell fill based on value of cell in row before
2
Replies
14
Views
301
saboh12617
S

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

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