Stonefield
New Member
- Joined
- Feb 10, 2018
- Messages
- 2
Hi,
I really hope someone is able to help me out. I have been looking for ways to resolve this excel challenge
I have an excel file with 3 worksheets (Overview, Input Data and Current Candidates on Project)
Overview is simple. It shows dates for the year, and start date on the left side. End date on the right side. Overviews should be depending on Input Data.
In the sheet Overview running totals is calculated by the sum of running totals by the previous day added by sale value added on the current day deducted with sale value of the candidates with an end date.
Overview
[TABLE="width: 500"]
<tbody>[TR]
[TD]Startdate[/TD]
[TD]Running totals[/TD]
[TD]Client[/TD]
[TD]Candidate name[/TD]
[TD]Sale Value[/TD]
[TD][/TD]
[TD]Enddate[/TD]
[TD]Client[/TD]
[TD]Running totals[/TD]
[TD]Sale Value[/TD]
[/TR]
[TR]
[TD]1-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Input Data
[TABLE="width: 500"]
<tbody>[TR]
[TD]Start date[/TD]
[TD]End date[/TD]
[TD]Client[/TD]
[TD]Candidate Name[/TD]
[TD]Sale Value[/TD]
[/TR]
[TR]
[TD]5-1-2018[/TD]
[TD]15-2-2018[/TD]
[TD]Test1[/TD]
[TD]John[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]10-1-2018[/TD]
[TD]28-2-2018[/TD]
[TD]Test2[/TD]
[TD]John2[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]5-1-2018[/TD]
[TD]15-2-2018[/TD]
[TD]Test3[/TD]
[TD]John3[/TD]
[TD]750[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Current Candidates on Project
[TABLE="width: 500"]
<tbody>[TR]
[TD]Start date[/TD]
[TD]End date[/TD]
[TD]Client[/TD]
[TD]Candidate Name[/TD]
[TD]Sale Value[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Question
Would it be possible to create a VBA script or any other solutions that would copy and paste data from Input Data to Sheet Overview and paste it to the corresponding data in the field depending on the data I fill in the sheet Input Date?
So if a candidate would start on 5-1-2018 and end on 15-2-2018 the corresponding data should go on Input Data on the 5th of jan as a stating candidate and at the same time added on the right on the 15th of feb as a ending candidate.
Next to the pasting there is one other challenge. What if there is already data on that date? Ideally I would line to insert a new row and add the data underneath it.
The last challenge is to also paste the candidates that are working TODAY should be displaced on the sheet Current Candidates on Project
Is something like this possible at all?
Your help is very much appreciated. I am stuck with finding a solutions.
Regards,
Rutger
I really hope someone is able to help me out. I have been looking for ways to resolve this excel challenge
I have an excel file with 3 worksheets (Overview, Input Data and Current Candidates on Project)
Overview is simple. It shows dates for the year, and start date on the left side. End date on the right side. Overviews should be depending on Input Data.
In the sheet Overview running totals is calculated by the sum of running totals by the previous day added by sale value added on the current day deducted with sale value of the candidates with an end date.
Overview
[TABLE="width: 500"]
<tbody>[TR]
[TD]Startdate[/TD]
[TD]Running totals[/TD]
[TD]Client[/TD]
[TD]Candidate name[/TD]
[TD]Sale Value[/TD]
[TD][/TD]
[TD]Enddate[/TD]
[TD]Client[/TD]
[TD]Running totals[/TD]
[TD]Sale Value[/TD]
[/TR]
[TR]
[TD]1-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Input Data
[TABLE="width: 500"]
<tbody>[TR]
[TD]Start date[/TD]
[TD]End date[/TD]
[TD]Client[/TD]
[TD]Candidate Name[/TD]
[TD]Sale Value[/TD]
[/TR]
[TR]
[TD]5-1-2018[/TD]
[TD]15-2-2018[/TD]
[TD]Test1[/TD]
[TD]John[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]10-1-2018[/TD]
[TD]28-2-2018[/TD]
[TD]Test2[/TD]
[TD]John2[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]5-1-2018[/TD]
[TD]15-2-2018[/TD]
[TD]Test3[/TD]
[TD]John3[/TD]
[TD]750[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Current Candidates on Project
[TABLE="width: 500"]
<tbody>[TR]
[TD]Start date[/TD]
[TD]End date[/TD]
[TD]Client[/TD]
[TD]Candidate Name[/TD]
[TD]Sale Value[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Question
Would it be possible to create a VBA script or any other solutions that would copy and paste data from Input Data to Sheet Overview and paste it to the corresponding data in the field depending on the data I fill in the sheet Input Date?
So if a candidate would start on 5-1-2018 and end on 15-2-2018 the corresponding data should go on Input Data on the 5th of jan as a stating candidate and at the same time added on the right on the 15th of feb as a ending candidate.
Next to the pasting there is one other challenge. What if there is already data on that date? Ideally I would line to insert a new row and add the data underneath it.
The last challenge is to also paste the candidates that are working TODAY should be displaced on the sheet Current Candidates on Project
Is something like this possible at all?
Your help is very much appreciated. I am stuck with finding a solutions.
Regards,
Rutger