Problem with dragging formula

CeeDom

New Member
Joined
Jan 31, 2014
Messages
5
I'm having a problem with dragging the formula so it adapts to each line. The problem is that I'm trying to show the result of a column of one book in another, but in book one the results are only in pairs cells, and in book it's in all cells.
For example:
Book 1:
K6
K8
K10

Book 2:

K6
K7
K8

The problem is that I put in the first two formulas like this
K6='Book 1'!K6
K7='Book 1'!K8

And then drag.
And then the results appear something like this:

K6='Book 1'!K6
K7='Book 1'!K8
K8='Book 1'!K8
K9='Book 1'!K10
K10='Book 1'!K10

And so on, always repeating.
Any suggestion for avoiding the repeat?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
try this in K6 of book 2. you can pull it down without having to adapt each time.

=OFFSET(book1!K$6,(ROW()-ROW(K$6))*2,0)
 
Upvote 0
try this in K6 of book 2. you can pull it down without having to adapt each time.

=OFFSET(book1!K$6,(ROW()-ROW(K$6))*2,0)

It still gives me the same problem, when pulling it down the next ones appear as:
K6
K8
K8
K10
K10...
 
Upvote 0
did you put the $ before 6's?

If you didnt use $, it will end up being exactly the same as your own formula.
 
Upvote 0
did you put the $ before 6's?

If you didnt use $, it will end up being exactly the same as your own formula.

With the $ it doesn't adapt at all, it just keeps repeating the selected cells, something like:
K6
K8
K6
K8
K6
K8

Man, i'm almost giving up and making the 1330 lines of formula manually...
 
Upvote 0
can you copy and paste your exact formula onto here please?
 
Upvote 0
can you copy and paste your exact formula onto here please?


Manually inserted:
='Março 2014'!AK4

vs the new code:
=DESLOCAMENTO('Março 2014'!AK$4(LIN()-(LIN(AK$4))*2,0);1;0)

Be aware that my office is Portuguese, so there are some differences, such as offset = deslocamento and row=lin
 
Upvote 0
Didn't read the last part of your post, not italian at all! lol


In the english versions of excel we use , to seperate the parts of formulas. It looks like you use ;
You've missed a ; out, which means its force you into the ;1;0 at the end...

try this:

=DESLOCAMENTO('Março 2014'!AK$4;(LIN()-(LIN(AK$4))*2;0)











 
Upvote 0
Didn't read the last part of your post, not italian at all! lol


In the english versions of excel we use , to seperate the parts of formulas. It looks like you use ;
You've missed a ; out, which means its force you into the ;1;0 at the end...

try this:

=DESLOCAMENTO('Março 2014'!AK$4;(LIN()-(LIN(AK$4))*2;0)



Now it gives me a #REF!, which is weird, because when I do the simple way, without the LIN()-(LIN(A$4)) it works fine. It just gives the repeat problem...










 
Upvote 0
Which row is the first instance of the formula being pasted into?

this part should equal 0 for the first instance.
(LIN()-(LIN(AK$4))*2

so basically it works if you are putting it in row 4.

you need to change the AK$4 in this section to whichever cell you are putting the 1st instance of the formula into.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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