I need help with a worksheet. I have 0 experience with VB, but i feel this should be rather simple.
Sheet1 is my Source sheet. Source sheet has a start date, and a list of students with values. Each student has name, 3 values and calculate button associated with him.
Sheet2 and Sheet3 are my data sheets, they have date, name of the student and 3 formulas - one for each source value. Formula is always SourceValue * X where X is the value of Date formula cell.
When I click "calculate" button the desire is to
1) set date on Data1 = source date + 7
2) set name of Data1 = source name
3) replace value of Date1 C5 cell = Source Value1 * by the content of C5 cell. for example if Source.Value1 is 100 and Date1.C5 = 0.5, I want Date1.C5 cell value to be replaced with 100*0.5 = 50
I can record macro for 1) and 2) and it works, but I can't figure 3) part. Looks like I can't attach files here , so here is dropbox link https://www.dropbox.com/s/jd4erxps4q5efn0/Date_transfer.xlsx?dl=0 and if this doesn't work either below is copy paste from the file.
Source sheet:
[TABLE="width: 466"]
<tbody>[TR]
[TD]Start Date:[/TD]
[TD="align: right"]9/14/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student Name[/TD]
[TD]Value1[/TD]
[TD]Value2[/TD]
[TD]Value3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]
<tbody>
</tbody>[/TD]
[/TR]
[TR]
[TD]Rick Johnson[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]
<tbody>
</tbody>[/TD]
[/TR]
[TR]
[TD]Katie Evans[/TD]
[TD]165[/TD]
[TD]210[/TD]
[TD]190[/TD]
[TD]
<tbody>
</tbody>[/TD]
[/TR]
</tbody>[/TABLE]
Data sheet:
[TABLE="width: 388"]
<tbody>[TR]
[TD]Start Date = SourceDate + 14 days[/TD]
[TD="align: right"]9/28/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student Name = SourceName of the student[/TD]
[TD] John Smith[/TD]
[/TR]
[TR]
[TD]Formula1 = Multiply SourceValue1 by:[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Formula2 = Multiply SourceValue2 by:[/TD]
[TD="align: right"]0.6[/TD]
[/TR]
[TR]
[TD]Formula3 = Multiply SourceValue3 by:[/TD]
[TD="align: right"]0.7[/TD]
[/TR]
</tbody>[/TABLE]
any help is much appreciated!
thanks
Sheet1 is my Source sheet. Source sheet has a start date, and a list of students with values. Each student has name, 3 values and calculate button associated with him.
Sheet2 and Sheet3 are my data sheets, they have date, name of the student and 3 formulas - one for each source value. Formula is always SourceValue * X where X is the value of Date formula cell.
When I click "calculate" button the desire is to
1) set date on Data1 = source date + 7
2) set name of Data1 = source name
3) replace value of Date1 C5 cell = Source Value1 * by the content of C5 cell. for example if Source.Value1 is 100 and Date1.C5 = 0.5, I want Date1.C5 cell value to be replaced with 100*0.5 = 50
I can record macro for 1) and 2) and it works, but I can't figure 3) part. Looks like I can't attach files here , so here is dropbox link https://www.dropbox.com/s/jd4erxps4q5efn0/Date_transfer.xlsx?dl=0 and if this doesn't work either below is copy paste from the file.
Source sheet:
[TABLE="width: 466"]
<tbody>[TR]
[TD]Start Date:[/TD]
[TD="align: right"]9/14/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student Name[/TD]
[TD]Value1[/TD]
[TD]Value2[/TD]
[TD]Value3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]
CALCULATE | ||
<tbody>
</tbody>
[/TR]
[TR]
[TD]Rick Johnson[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]
CALCULATE | ||
<tbody>
</tbody>
[/TR]
[TR]
[TD]Katie Evans[/TD]
[TD]165[/TD]
[TD]210[/TD]
[TD]190[/TD]
[TD]
CALCULATE | ||
<tbody>
</tbody>
[/TR]
</tbody>[/TABLE]
Data sheet:
[TABLE="width: 388"]
<tbody>[TR]
[TD]Start Date = SourceDate + 14 days[/TD]
[TD="align: right"]9/28/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student Name = SourceName of the student[/TD]
[TD] John Smith[/TD]
[/TR]
[TR]
[TD]Formula1 = Multiply SourceValue1 by:[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Formula2 = Multiply SourceValue2 by:[/TD]
[TD="align: right"]0.6[/TD]
[/TR]
[TR]
[TD]Formula3 = Multiply SourceValue3 by:[/TD]
[TD="align: right"]0.7[/TD]
[/TR]
</tbody>[/TABLE]
any help is much appreciated!
thanks