Skipping x number of cells in excel?

[mindmaniac]

All I want to do is make a formula that when dragged down skips a certain number of cells. For example the first displays A1, then next A3, then A5 .... Is there anyway to tell excel to say =A3+2 without it actually thinking it's a math formula?

Also I need to drag the cells horizontally, but I would like them to transpose without using that poor method of copy and pasting. Eg. Display B1, next B2, next B3...

 

[rch4001]

if I understand your question, the drag down is easy.

in the top cell type "a1" and in the cell beneath that type "a3". select both and drag down from the bottom right corner of the a3 cell. it should keep the first letter and increment the last digit by 2 so you get

a1
a3
a5
a7
etc

cant think of any easy way to drag across since you want to change the first character and xl wants to change the last character when you drag.

 

[Kelnoen]

you can use maths on cell numbers by changing the sheet to use 'R1C1' style links.

Tools > Options > General

Tick the 'R1C1' option.

This refers to Rows and Columns, eg. R4C7 = Row 4 Column 7 (or G4).

You can then refer to cells using maths eg. R4C[-2] in G7 will refer to Row 4 Column 5 (7 - 2 = 5) so it will refer to G5.

More here: http://www.bettersolutions.com/excel/EED883/YI416010881.htm

 

[mindmaniac]

Kelnoen, I think you've got something, but I still can't figure out how to tell it to skip cells when I start dragging it down.

 

[Kelnoen]

I'm still not 100% what your trying to do, can you give en example?

 

[mindmaniac]

I have a formula that needs to be dragged down. Every time it goes down though I need it to reference the next cell 18 down. So if the formula starts =A1, the next needs to be =A19, then =A37. It's not that simple of a formula, but that is the simplified version of what i need. I've got 18 columns of this and it needs to go 30 rows down, so doing this manually will take forever.

 

[KLin]

Try typing in the formula for A1 and A19 manually. Highlight them, then drag down from there. Excel should be smart enough to see the pattern and continue it on.

 

[mindmaniac]

Originally posted by: KLin
Try typing in the formula for A1 and A19 manually. Highlight them, then drag down from there. Excel should be smart enough to see the pattern and continue it on.

You would think it would catch on, but it doesn't. So far it looks like there is no solution.

 

[PowerEngineer]

Okay, I admit this isn't a clean solution but it's the kind of thing I've been forced into in similar situations...

One way to attack it is to first put a string of integers from 1 to however many you need to go down in column A (i.e. 1,2,3,4...X). If the cells you want to reference are in column D (i.e. D1, D19, D37,...), then reference them in cell B1 as:

=OFFSET($D:$D,18*($A1-1)+1,0,1,1)

Of course, this can be part of a more complex formula. Because the $A1 will point to the subsequent integers in column A as you copy it down, the results in column B should be what you want.

Not exactly what you want, but it gets you there... :thumbsup:

 

[William V. Rosales]

Good afternoon,

Still today we do not have an answer to this question? I could do it with the offset by creating an auxiliary column with the variation I want to use on my filling. Like this, if I want to drag down and have A1, A4, A7 etc., I create a column that goes from -3 (4-1 from A[4] to A[1]) to 0, then 3, 6, 9 etc. So, I made this formula =DESLOC($a$1;(B60+3);0;1;1), with e19 being the first value I want to copy, and b60 being -3, the first term of the auxiliary column.

Even though, I wanted to discover an automatic solution for both problems @mindmaniac cited, both seem no to be complex and would be extremely utile to be automatically done.

Thanks

 

[Puffnstuff]

Perhaps this article might be of some help to you.

https://excel.tips.net/T005405_Skipping_Rows_when_Filling.html

 

[sashaman14]

I figured it out...easily..
Start a column next to the one you want to skip. number by 1, blank, 2, blank.... (assuming you want to skip every other). Drag/skip down.
Highlight both...then you sort, lowest to highest,