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?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

CROY1985

Active Member
Joined
Sep 21, 2009
Messages
498
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)
 

CeeDom

New Member
Joined
Jan 31, 2014
Messages
5
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...
 

CROY1985

Active Member
Joined
Sep 21, 2009
Messages
498
did you put the $ before 6's?

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

CeeDom

New Member
Joined
Jan 31, 2014
Messages
5

ADVERTISEMENT

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...
 

CeeDom

New Member
Joined
Jan 31, 2014
Messages
5

ADVERTISEMENT

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
 

CROY1985

Active Member
Joined
Sep 21, 2009
Messages
498
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)











 

CeeDom

New Member
Joined
Jan 31, 2014
Messages
5
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...










 

CROY1985

Active Member
Joined
Sep 21, 2009
Messages
498
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,378
Messages
5,595,827
Members
414,024
Latest member
Woodsa

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