Dragging formula right - want to read through a column and then skip some columns to read the next data

[kirklan]

Hi everyone,
I need something which sounds complicated and maybe is but hopefully someone will get me.

I use two worksheets - Goals and Results.
In Results I have two columns with goals scored at home and away.
In Goals I got a table for both home and away games in which I want to read and input the data from Results.

And I got two (or three) problems with it.
1) The Results data are in columns but I want to drag the formula in Goals to the right (would change columns rather than row numbers)
2) I want to skip 4 columns when I get to the next team.
3) And I would like all this wrapped up into an IF function - if the cell is blank (the match has not been played yet) I want the cell with the formula in Goals to show "N/A" (i worked this one out alone but I am not sure if I would be able to do that with all the stuff I would like to add now).

It is kind of complicated but I want to have results in one worksheet and do several stats from that (that is why I want to skip so many columns too). I will better add a file so you can get a better perception of it. An example of the function using cells A3:A16 would be enough (I added data for the next team so you could test out dragging it all down).

If anyone would be able to solve this, I would be really greatful!
Regards,
kirklan

https://dl.dropboxusercontent.com/u/32967536/example.xlsx

 

[QU4487]

I'm struggling to understand exactly what your end result should be. Would it be possible to add to the example a hoped for outcome? I can think of a number of ways to improve the worksheet layout, but without knowing the intended end result, wouldn't wish to waste either of our time.

 

[kirklan]

Well, basically I wanted to have a function rewriting the column A from results13 (A3:A17) to row 7 in Goals 2013 (D7:R7) and adding N/A where the cell is originally blank.

Additionally when dragging this formula down to skip 4 columns from results13 - that means reading from F3:F17 when writing into Goals D8:D15 and so on.

I was too impatient so I solved the first two by adding an additional worksheet and arraying a transpose function but if you could provide me with some clue how to skip 4 rows (yeah, rows now, transposed the original :D ) when dragging down would be sufficient now.

Using example formulas - I have this ranging from C to Q:

=IF('results13(transposed)'!C1="n";"N/A";'results13(transposed)'!C1)

and when I drag this down I want to get immediately

=IF('results13(transposed)'!C5="n";"N/A";'results13(transposed)'!C5)

then C9, C13 and so on.

 

[oldbrewer]

[TABLE="width: 848"]
<colgroup><col span="7"><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]myrefcell[/TD]
[TD]goalsfor[/TD]
[TD="colspan: 2"]goalsagainst[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]teama[/TD]
[TD]teamb[/TD]
[TD]teamc[/TD]
[TD]teamd[/TD]
[TD]teame[/TD]
[/TR]
[TR]
[TD]teama[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]goalsfor[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]teamb[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]goalsagainst[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]teamc[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]teamd[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]teame[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]the formula in the cell for teama goalsfor (j10) is[/TD]
[TD="colspan: 3"]=OFFSET(myrefcell,J$8,1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]the formula in the cell for teama goalsagainst (j11) is[/TD]
[TD="colspan: 3"]=OFFSET(myrefcell,J$8,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]this is a simple way of automatically collecting data and switching it from rows to columns[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kirklan]

One question about that. myrefcell is a table name here or I write that into a cell?

 

[kirklan]

Was a stupid question, figured it out.

 

[oldbrewer]

yes i named the cell with myrefcell in it "myrefcell"

 

[kirklan]

Thx very much, this really helped me understand the offset function.
Would you please similarly explain me how to skip rows (when dragging down) or columns (when dragging right)?

 

[oldbrewer]

Would you please similarly explain me how to skip rows (when dragging down) or columns (when dragging right)?

I do not understand what you are saying here. If you highlight A1:C1 and drag down 5 rows you have copied to next 5 rows - if you didnt want it in row 4 tough!

A simple macro will copy a row to every row you want it to leaving gaps where necessary.

 

[kirklan]

Nah, I want to be dragging down and skip rows like this
...
I have C1 in the function, if I drag it down I get C2 but I want C5 and C9 on the 3rd row and C13 on the 4th row and...