Excel copy and pasting depending on date range / input of date

[Stonefield]

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

Startdate	Running totals	Client	Candidate name	Sale Value		Enddate	Client	Running totals	Sale Value
1-1-2018
2-1-2018
3-1-2018
4-1-2018

<tbody>
</tbody>

Input Data

Start date	End date	Client	Candidate Name	Sale Value
5-1-2018	15-2-2018	Test1	John	400
10-1-2018	28-2-2018	Test2	John2	500
5-1-2018	15-2-2018	Test3	John3	750

<tbody>
</tbody>

Current Candidates on Project

Start date	End date	Client	Candidate Name	Sale Value

<tbody>
</tbody>

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

 

[jim may]

What appears on the Sheet InputData is 100% Input - No changes are made to it from the proposed Macro; Right?

The Overview Sheet AND the Current Candidates on Project ARE BOTH updated from the Proposed Macro; Right?

Show us by filling in to the two updatable sheets Overview & CConP the Info as it shoud appear AFTER Running the Macro!!

Regards,

Jim

 

[Stonefield]

What appears on the Sheet InputData is 100% Input - No changes are made to it from the proposed Macro; Right?

The Overview Sheet AND the Current Candidates on Project ARE BOTH updated from the Proposed Macro; Right?

Show us by filling in to the two updatable sheets Overview & CConP the Info as it shoud appear AFTER Running the Macro!!

Regards,

Jim

Hi Jim,

Thank you for replying so quickly. Great that you are willing to have a look.
The Input Data is indeed 100% input. No changes to be made.
Overview and Current Candidates are both updated from the proposed macro.

Below is the ideal situation with first the Input Data field, then the Overview and Current Candidates.
I added Employee name and input data to calculate sale value.

Overview
(sale value = ((margin*hours per week)-(Shrinkage*margin*hours per week)

Startdate	Enddate	Employee name	Client	Candidate name	Bill	Pay	Margin	Hours per week	Shrinkage (%)	Sale value
3-1-2018	4-1-2018	abc	Test1	John	100	95	5	40	15,00%	170
5-1-2018	7-1-2018	def	Test2	John2	85	75	10	40	15,00%	340
6-1-2018	9-1-2018	def	Test3	John3	80	67,5	12,5	40	15,00%	425
5-1-2018	7-1-2018	ghj	Test4	John4	60	52,5	7,5	40	15,00%	255

<colgroup><col span="2"><col><col><col span="4"><col><col><col></colgroup><tbody>
</tbody>

Overview

Startdate	Running totals	Employee name	Client	Candidate name	Sale Value		Enddate	Employee name	Client	Candidate name	Sale Value
1-1-2018	€ 0,00						1-1-2018
2-1-2018	€ 0,00						2-1-2018
3-1-2018	€ 170,00	abc	Test1	John	170		3-1-2018
4-1-2018	€ 170,00						4-1-2018	abc	Test1	John	170
5-1-2018	€ 595,00	def	Test2	John2	340		5-1-2018
		ghj	Test4	John4	255
6-1-2018	€ 1.020,00	def	Test3	John3	425		6-1-2018
7-1-2018	€ 1.020,00						7-1-2018	def	Test2	John2	340
								ghj	Test4	John4	255
8-1-2018	€ 680,00						8-1-2018
9-1-2018	€ 680,00						9-1-2018	def	Test3	John3	425
10-1-2018	€ 255,00						10-1-2018
11-1-2018	€ 255,00						11-1-2018
12-1-2018	€ 255,00						12-1-2018

<colgroup><col><col span="2"><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Current candidates on project

Date Today	Startdate	Enddate	Employee name	Client	Candidate name	Sale Value
6-1-2018	5-1-2018	7-1-2018	def	Test2	John2	340
	6-1-2018	9-1-2018	def	Test3	John3	425
	5-1-2018	7-1-2018	ghj	Test4	John4	255
						1020	Total

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

If I can help in any way please let me know!

Rutger

 

[Fluff]

@Stonefield

Please do not post the same question multiple times. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.

I have deleted your other 2 replies to this thread