Repeating sequence, how?

[pepijnkr]

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

[Moo the Dog]

Well, if you just slightly modified your current sheet (Blad1) to include the ID on all three lines as in my example workbook (attached), you could simply use this INDEX formula in cell D1, then fill it down:

=INDEX(Blad2!$B$2:$D$4,MATCH(B1,Blad2!$A$2:$A$4,0),MATCH(C1,Blad2!$B$1:$D$1,0))

- Moo

[davsth]

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

Regards

Dav

[Pete_UK]

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

[Moo the Dog]

Pete, making it look so simple yet again.

[Pete_UK]

Hi Moo,

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

Pete

[Pete_UK]

@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

[Moo the Dog]

I try to pop in from time to time... kids are growing up, and lots of other stuff to fill the hours.

- Moo (Vince)

[pepijnkr]

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.

[Pete_UK]

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

[pepijnkr]

Still doesn't work

[Phuocam]

Still doesn't work

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

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

[Pete_UK]

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

A week ago !!

Pete

[Pete_UK]

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

Pete

[joris moerings]

Still doesn't work

You probably didn't take Pete's instruction literally because your file has double quotes surrounding the 9^9. Remove the double quotes surrounding by repeating the search and replace

Find what: "9^9"
Replace with: 9^9
Click Replace All and make sure you insert the phrases in the Find What inputbox exactly as shown.

[joris moerings]

Still doesn't work

You probably didn't take Pete's instruction literally because your file has double quotes surrounding the 9^9. Remove the double quotes surrounding by repeating the search and replace

Find what: "9^9"
Replace with: 9^9
Click Replace All and make sure you insert the phrases in the Find What inputbox exactly as shown.

[JohnTopley]

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

[pepijnkr]

Thnx everyone

closed

[Pete_UK]

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