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
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
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:- MooFormula:Please Login or Register to view this content.
=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
Pete, making it look so simple yet again.
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
I try to pop in from time to time... kids are growing up, and lots of other stuff to fill the hours.
- Moo (Vince)
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
Congratulations! My son will be 13 tomorrow, and my daughter will be 7 in a few days... so I have a few years before I have to worry about that
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
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
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.
HtH,
Joris
--------------------------------------------------------------------------------
If you found my answer helpful, please hit the Like or Thank button.
Please follow the forum Rules and Guidelines and use Code tags around your VBA code.
Remember: test VBA code always on a copy of your file because usually you can't use undo
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.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks