dan0s
03-20-2009, 04:44 AM
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)
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)