Copying formulas where source "jumps" in bigger gaps than target.

Originally Posted by Flaxkrax

Hmm.. maybe a akward headline... But I want to do the following;

sheet1 contains info ranging like A4, A8, A12, A16.... A200

sheet2 shall display the value of the mentioned cells from sheet1, but in the following place D2, D4, D6, D8, D10.... D100

So if I copy the formula from cell Sheet2.D2 to Sheet2.D4 it says =Sheet1.A6 since I only moved two steps down in Sheet2 but wanted to go 4 in Sheet1. I want it to edit the "steps" taken when copying, possible??

It seems so basic but I just can't figure out how to do it....

Please help, or I will have manually edit 100+ cells....

/Johan

One way would be to copy via a spare sheet, copy the cell required and paste to the same address of a spare sheet. Formula-fill sideways/downwards to achieve the address references required, select that cell, then select the formula from the formula bar (click-drag across the formula, then CTRL-C)
Press ESC to clear the 'copy-from' glow, and Paste into the required cell.

Some simple formula will be easier to edit after a direct paste, others can be done by this method.

Lets hope someone else has a better plan.
---

I wish it would be as simple editing the copy function as in fill/series where you adjust the increments. That would do it for me... Grr...

Originally Posted by Flaxkrax

I wish it would be as simple editing the copy function as in fill/series where you adjust the increments. That would do it for me... Grr...

Can you give an example of a formula, and the cell address 'from' & 'to' and the adjustments that would need to be made?

---

OK, I'm making a scoresheet to a tournament. On one sheet I have the tree, which is a 256 participant sheet (and a request is even made for a 512 sheet.) On the other sheet you have the scheduled times when each game start.

The schedule reads the name of the participant from the tree sheet, and I have automated moving of the winning players there as well.

The schedule sheet has info on every line, and the tree has info on every second line.
Schedule sheet

should project the following info
A B C
1 playA. vs playB.
2 playC. vs playD.
3 playE vs playF.

Score sheet

contains the following info
A B
1 Player A empty
2 empty
3 Player B
4 empty
5 Player C
6 empty
7 Player D
8 empty
9 Player E
10 empty
11 Player F

Column B contains no useful information regarding this issue.

The formula of the scoresheet in cell A1 =IF(ISTEXT($tree.A1);T($tree.A1);"") now I want to copy or fill the whole score sheet with this formula on row A4...A100.
Since the next row on A2 =IF(ISTEXT($tree.A5);T($tree.A5);"") I can't make it do those "increments of four" step jumps all the way down to row A100 =IF(ISTEXT($tree.A400);T($tree.A400);"") in any kind of way... If I copy a lager portion of cells it works, but starts out at the wrong value, hence making me edit it manually.. Frustrated.. soo many cells to do it manually...

I can see that my table got messed up when posting, but I think you understand anyway.

Originally Posted by Flaxkrax

OK, I'm making a scoresheet to a tournament. On one sheet I have the tree, which is a 256 participant sheet (and a request is even made for a 512 sheet.) On the other sheet you have the scheduled times when each game start.

The schedule reads the name of the participant from the tree sheet, and I have automated moving of the winning players there as well.

The schedule sheet has info on every line, and the tree has info on every second line.
Schedule sheet

should project the following info
A B C
1 playA. vs playB.
2 playC. vs playD.
3 playE vs playF.

Score sheet

contains the following info
A B
1 Player A empty
2 empty
3 Player B
4 empty
5 Player C
6 empty
7 Player D
8 empty
9 Player E
10 empty
11 Player F

Column B contains no useful information regarding this issue.

The formula of the scoresheet in cell A1 =IF(ISTEXT($tree.A1);T($tree.A1);"") now I want to copy or fill the whole score sheet with this formula on row A4...A100.
Since the next row on A2 =IF(ISTEXT($tree.A5);T($tree.A5);"") I can't make it do those "increments of four" step jumps all the way down to row A100 =IF(ISTEXT($tree.A400);T($tree.A400);"") in any kind of way... If I copy a lager portion of cells it works, but starts out at the wrong value, hence making me edit it manually.. Frustrated.. soo many cells to do it manually...

Can I presume this is a Mac? - the codes being slightly different.

=IF(ISTEXT($tree.A5);T($tree.A5);"")
I would understand as
=If(IsText(Sheet1!A5),A5,"")

but that should not affect your question.

You should be able to 'jump' your formula, but that would depend upon whether the intervening 3 lines required separate formula, 4 different sets (are possible) but could get messy.

To 'jump' as you ask, work on the Mod of the Row()

thus, you want the formuls to action every 4 rows,

=If(Mod(Row(),4)=0,do-every-4th,other.row)

thus, your formula becomes
=If(Mod(Row(),4)=0,$tree.A5,other.row)

but you want to select the correct row, which is =Int((Row()-1)/4)+1

whereby Row 1 from $Tree A column appears in row 4 of this sheet
and Row 2 from $Tree A column appears in row 8 of this sheet.
and row 100 appears on row 400

=If(Mod(Row(),4)=0,Indirect("$tree.A"&5),other.row)
becomes
=If(Mod(Row(),4)=0,Indirect("$tree.A"&(Int((Row()-1)/4)+1)),other.row)

You would need to add the 'blank' test to that, but then agai, you knew that this was not going to be easy, just 'useful' (maybe).

Is this the right idea?

---