Copy cells, change in the reference only sheet; same cell

[pablo r.]

Hi,

some cryptic Subject. Here's my question/problem.

First the structure of my file

Sheet 1

c1 empty
c2 empty
c3 empty
c4 empty
c5 10
c6 empty
c7 empty
c8 empty
c9 empty
c10 17


Sheet 2

c1 empty
c2 empty
c3 empty
c4 empty
c5 12
c6 empty
c7 empty
c8 empty
c9 empty
c10 21



As you can see there is every 5th cell a value, I like to show all those values in one table in a new sheet. (of course there are a lot more values than in this example, unfortunenately)

That should look like

Sheet 3

a1 ='sheet 1'c5
a2 ='sheet 1'c10
a3 ='sheet 1'c15

b1 ='sheet 2'c5
b2 ='sheet 2'c10
b3 ='sheet 2'c15

Thats all, not so difficult I thought.

But I have to write down every single cell by hand, I found no automatism.

Here what I've tried:

Mark a1 in sheet 3 and pull it on the right edge down:

a1 ='sheet 1'c5
a2 ='sheet 1'c6
a3 ='sheet 1'c7

not what I was looking for

Mark a1 to a3 in sheet 3 and pull it on the right edge down:

a1 ='sheet 1'c5
a2 ='sheet 1'c10
a3 ='sheet 1'c15
a4 ='sheet 1'c5
a5 ='sheet 1'c10
a6 ='sheet 1'c15
a7 ='sheet 1'c5
a8 ='sheet 1'c10
a9 ='sheet 1'c15

not what I was looking for

Two things I think that coul work but I dont know how:

1:
pull down and calc will automatically fill in 5; 10: 15; 20; 25 instead of repeating 5;10;15; 5;10; 15


2:
I already made the row for sheet 1 but there are 10 more sheets to go in the real file so I can copy that row and replace 'sheet 1' by 'sheet 2' in every formula.
But it is a lot of work to do it by hand. Is there a possibilty like "search and replace" in writer-documents for formulas? A tool where I can say search for the words 'sheet 1' in all formulas in row b on sheet 3 and replace it with the words 'sheet 2'.

Thanks
Best Regards
and I'm sure the answer is simple

PS: As you may see my native language is not english, so please keep it simple.

[David]

I'm a bit busy for the next little while, and it is not at my fingertips to be able to tell you directly, but someone might come up with a use for the OFFSET() function.

David.

[pablo r.]

If the table shows


a1 5
a2 10
a3 15

and I mark a1 to a3 and pull it down I will get

a4 20
a5 25 and so on.

That's exactly what I need for a cell refer

a1 =b5
a2 =b10
a3 =b15

the next shoulb be

a4 =b20
a5 =b25

but I am not able to find a button or something to make this automatically, I have to do it by hand.

Thanks

[David]

To refer to your question specifically:

Select those first three cells, A1, A2, A3, in which you have 5, 10, 15. They should turn dark. Hover the mouse over the right lower corner of that selection, and the cursor will change to a "+". Press the left-mouse key, and drag down the column.

Method 2, perhaps better in your case:

Select all of column A by clicking in the A at the top of the column. Now choose Edit/ Fill/ Series. Choose the Start value to be 5, the Increment 5, and choose a suitable end value [a multiple of 5.]

David.

[pablo r.]

Thanks,

that's what I want to do with a REFER in a cell. The same tool OO is using for a row of values with a simple pattern I need for references in a cell with a simple pattern (A1; A6; A11;...)


my values ain't 5, 10, 15.
I tried to explain it in my first message, but I'm explaining it sometimes a little bit confused, so I try again.

Sheet 1

a1 230
a2 547
a3 345
a4 234
a5 769
a6 403
a7 506
...

as you see there is no pattern/system in it.

I need a table that shows every 5th value of that row.

Sheet 2

a1 ='sheet 1'a1
a2 ='sheet 1'a6
a3 ='sheet 1'a11
...

that row is very long and I found no way how OO fills in automatically
a4 ='sheet 1'a16
I have to do it by hand.

dozens of times in my case.

Any suggestions?

[David]

I'm keeping it simple [for myself]. You can adjust to suit, if this works for you:

I have some data in columns A [some letters] and B [some numbers.] I want to collect [assimilate] only every fifth piece of data from columns A and B. So, the content of A5 will appear in cell C1, of A10 in C2, of A15 in C3, and so on. That of cell B5 will appear in D1, of B10 in D2, of of B15 in D3, and so on.

In C1 have this: =OFFSET($A$1;ROW()*5-1;0)

In D1 have this: =OFFSET($A$1;ROW()*5-1;1)

Select both and the lower right little black square should change to a "+" when you hover over it with the mouse. Click on that and drag down the columns to copy as far as needed.

Is this the sort of thing to which yo are referring?

David.