Repeating sequence, how?

please help

D1: =Blad2!B2
D2: =Blad2!D2
D3: =Blad2!F2

I want to repeat this sequence as following
D4: =Blad2!B3
D5: =Blad2!D3
D6: =Blad2!F3

D7: =Blad2!B4
D8: =Blad2!D4
D9: =Blad2!F4

How do i do this

=OFFSET(Blad2!$B$1,TRUNC(ROW(A3)/3),MOD(ROW(A3),3)) in cell D1 and copy down

Regards

Dav

You can use this in D1 of Blad1:

=INDEX(Blad2!$B:$D,INT((ROWS($1:1)-1)/3)+2,C1)

then copy down.

Hope this helps.

Pete

Hi Moo,

good to see you back - I noticed you posting again the other day.

Pete

@pepijnkr,

I see you are based in Holland, so you probably need to use semicolons ( ; ) in the various formulae, rather than the commas ( , ).

Also, you show your Excel version as being 2000, which is why I didn't wrap IFERROR( ... ,"") around my formula. If you are using a later version, please update your profile.

Hope this helps.

Pete

Well, I just found out over Christmas that my eldest daughter is expecting a baby in the summer, so grandparents' duties might start to take over after that !!

Pete

Thanks guys for all the help!

As i am a noob with the MATCH command, I still don't get how i can copy the formula from my example sheet to my original file. That's why i posted a short fragment of my original file. I hope somebody can help me to fix the MATCH command in my original file.

On page 1 of the example2 file I made a list next to the table of what cells should contain what.

Hope you guys can help me!

I've had to change the entries in D1 to G1 of Blad1, and also in column C, as can be seen in the attached file, and then I used this formula in cell D2:

=INDEX(Blad2!$B$2:$M$5,MATCH(LOOKUP("zzzz",$B$2:$B2),Blad2!$A$2:$A$5,0),MATCH(D$1&"_"&$C2,Blad2!$B$1:$M$1,0))

(This might appear slightly different to you as I expect the function names will get translated into Dutch, and the commas will change to semicolons).

This formula can then be copied across and down to fill your table, although you will have to re-instate the formats (lines) afterwards.

Hope this helps.

Pete

Thanks again for the help.

Still trying to get it to work, I uploaded my original file with this post.
Don't know why but i can't get it to work.

Thanks.

You have changed your IDs from text values to numbers, so the part of the formula that looks like this in D2:

LOOKUP("zzzz",$B$2:$B2)

no longer works - it should be changed to this:

LOOKUP(9^9,$B$2:$B2)

(The 9^9 is just a big number, larger than the values that you are using, in the same way as "zzzz" was just a large text value in the other formula).

You can make the necessary changes quite easily in one operation using Find & Replace (CTRL-H). First, highlight the columns D to G in Blad1, then CTRL-H, and:

Find what: "zzzz"
Replace with: 9^9
Click Replace All

then you should see all those #N/A results change to the appropriate values.

Hope this helps.

Pete

Still doesn't work

Try

=INDEX(Blad2!$B$2:$M$756,MATCH(LOOKUP(9^9,$B$2:$B2),Blad2!$A$2:$A$756,0),MATCH(D$1&"_"&$C2,Blad2!$B$1:$M$1,0))

no quotes round 9^9 as you are comparing numbers not text

Originally Posted by pepijnkr

Still doesn't work

Change LOOKUP("9^9",$B$2:$B2)

To LOOKUP(9^9,$B$2:$B2)

Isn't that what I said in Post #14 ?

A week ago !!

Pete

AND, after some delay, I received 14 email notifications of Post #15. Sheesh !!

Pete

Thnx everyone

closed

A couple of things, then, to wrap things up:

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum (in terms of posts), you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete