Not skipping cell when dragging or copying

Hello,


I am new, but hope for your help with Excel 2010


I am doing a sheet for analyzing projected financial plan, actual results and deviations. The problem is that I have a forecast for 40 years on a monthly basis and I can not do such sheets manually. I must to drag it.

Let's start with one case.

MAIN SHEET - Analysis of performance
A = PLAN = "= PLAN_B! C2"
B = RESULTS = "= RESULTS_B! C2
C = Deviation from plan = A-C
And so on in right for each another month.

I want to drag this right three columns and they like to analyze each value from different sheet and calculate the deviation.

So it Should be
A2 = PLAN_B! C2 | B2 = RESULTS_B! C2 | C2 = A2-C2
D2 = PLAN_B! D2 | E2 = RESULTS_B! D2 | F2 = D2-E2
G2 = PLAN_B! E2 | H2 = RESULTS_B! E2 | I2 = G2-H2
.... and so on.

Unfortunately, when I drag it to the right in Excel, the values ​​in the linked sheets also jump at three positions which is
A2 = PLAN_B! C2 | B2 = RESULTS_B! C2 | C2 = A2-C2
D2 = PLAN_B! F2 | E2 = RESULTS_B! F2 | F2 = D2-E2
G2 = PLAN_B! I2 | H2 = RESULTS_B! I2 | I2 = G2-H2

Well, this is bad, because the source sheet which takes three columns ..



Please help me.

Hi and welcome to the forum

I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Select C7:E7. With the cursor grab the little square in the lower right corner of the selection by hovering over the square until the cursor becomes a cross then press and hold the left mouse button and drag to the right as far as you want. The 3 cell selection will be repeated as far as you drag. Now with the row still selected, grab the same "handle" and drag down.

Would this result be what you want?
I tried to upload the result but it was just too large.

Hi Radoslawz,

You can fill them with this macro:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

And, you should be ready to go

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

I don't know what you are doing differently, but it works perfectly here.

Hi Radoslawz,

I'll see if I can speed that up by using Worksheet Arrays!

Edit:

This takes about a second on your sample

Please Login or Register  to view this content.

OK see what you mean. Totally misunderstood the problem.

The first macro only took a minute on your sample??? But, I speeded it up - so 40 seconds vs 40 minutes???

Try this in C7...
=INDEX(RESULTS_B!$C$7:$LN$132,ROW(A1),MATCH(C$5,RESULTS_B!$C$6:$LN$6,0))
and this in D7
=INDEX(PLAN_B!$C$7:$LN$132,ROW(A1),MATCH(Analysis!C$5,PLAN_B!$C$6:$LN$6,0))

Copy both down, and then copy the pair over as far as you need to go

Copy both down, and then copy the pair over as far as you need to go

Perhaps you missed that part? copy, dont drag

The macro can be triggered with a key or a button to ease its use