Solved: Need help te create formula or vba code

[dan0s]

Hello,

For the last two weeks I have been strugling to get my document to what it is today. I am new to VBA but so far I have succeeded to get my buttons to work for what I want them to do. I am learning a lot right now by reading/googling/asking people when I have problems. Right now, after two days of trying to get a (regular) formula to work, I am stuck. Here is my problem.

I have two sheets. First is "Risico Analyse" from which to get data and second "Risico-reductie" where the data should appear based on a Ja/Nee selection on the first sheet.

People using this sheet will enter data, and then select in column T if the data should be transferred for discussion. If so, required data (data left of Ja selection, rows K, L-N, R and S should get copied, and then pasted down in sequence starting in A16 at the second sheet.

I've tried doing this with a Formula based on an example document which I also attached to the post. The used formula is:

=IF(COUNTIF(Blad1!$D$2:$D$27;Blad1!$O$1)<ROW()-ROW(Blad2!A$2)+1;"";INDEX(Blad1!A$2:A$27;SMALL(IF(Blad1!$D$2:$D$27=Blad1!$O $1;ROW(Blad1!$D$2:$D$27)-ROW(Blad1!A$2)+1;"");ROW()-ROW(Blad2!A$2)+1)))

I editted it to fit my requirements but did not succeed. I know that what I want can be done with a Formula, but also using the if function with a VBA update button. I do not know what is wise to do. Building a cannon for a mosquito (vba) or getting a proper working Formula. I haven't used the formula section of Excel so I don't know how to make the formula.

Can someone please help me with this issue? I am not allowed to post hyperlinks yet (which would make my explanation a lot easier), but I attached the following file:

Stripped version of the document I am using (example stripped v1.36.xls)


I am using excel 2003 (english)

Thank you in advance.

with kind regards

Daan (The Netherlands)

[Bob Phillips]

What dat should be copied over, and where to?

[dan0s]

What dat should be copied over, and where to?

Only data from sheet1 ( Risico-analyse) answered with Ja in column T for example:

K25 sheet1 to row A.16 sheet2 (Risico-reductie)
L25 (merged cells) sheet1 to row B16 sheet2
R25 (value) sheet1 to E16 sheet2
S25 (value) sheet1 to F16 sheet2

and so on.

I made some jpg files and have a example document of what I want but cant add hyperlinks yet due to my low number of posts. Added example of what i want in this reply!

--edit--

Data can also be copied acording to the 1, 2, 3 ETC in column K.

so if K is larger then 0 select row K+L+R+S of larger values
copy selection
go to sheet 'risico-reductie'
paste.special xlvalues starting from A16

I don't know but something like that might be better?

This function is done with a formula in the example sheet.

[dan0s]

Can somebody please help me? I added a jpg which should clarify what I am looking for.

I don't know how to create this formula

Any help or suggestions are appreciated!

--edit--

After a lot of searching I believe this can be done using Vlookup.. I am currently trying to get this to work.

[dan0s]

Solved it using vlookup on column K.