Excel Spreadsheets - Data transfer
Thread Starter
Joined: Aug 2001
Posts: 28
Likes: 0
From: UK
Excel Spreadsheets - Data transfer
I’m after some help regarding transferring data from one worksheet to another worksheet in the same workbook
What I have at the moment is a Personnel Holiday Workbook containing 9 worksheets. Sheet 1 is a yearly calendar with the names of each person annotated in column A, this carries on down the page for each month.
The other worksheets (2-9) are against the individual’s name, each sheet containing a grid from 1-28 (number of days holiday entitlement)
When holiday is booked, the dates requested are put in the grid on the sheet assigned to the person - (1 box = 1 date)
I would like to know if there is a way of transferring the number of days that have been booked, on to the calendar sheet (sheet 1). If 1/1/10 & 2/1/10 are booked holiday I would like sheet 1 to show 1 & 2 against the persons name and on the correct dates.
I hope you are still with me, and would be grateful for any info on how to achieve this.
What I have at the moment is a Personnel Holiday Workbook containing 9 worksheets. Sheet 1 is a yearly calendar with the names of each person annotated in column A, this carries on down the page for each month.
The other worksheets (2-9) are against the individual’s name, each sheet containing a grid from 1-28 (number of days holiday entitlement)
When holiday is booked, the dates requested are put in the grid on the sheet assigned to the person - (1 box = 1 date)
I would like to know if there is a way of transferring the number of days that have been booked, on to the calendar sheet (sheet 1). If 1/1/10 & 2/1/10 are booked holiday I would like sheet 1 to show 1 & 2 against the persons name and on the correct dates.
I hope you are still with me, and would be grateful for any info on how to achieve this.
Joined: Mar 2006
Posts: 495
Likes: 0
From: Chez Sprog
Sounds like a job for COUNTIF. Count will (surprise surprise) count the occurrences of data in a range of cells & display the number of occurrences in the cell you designate.
So if you place the letter 'x' in the holiday grid & then in sheet one, col B, place the countif formula, it should return the number of times an x has been placed in the grid - 5 x's returns a value of 5, showing that Mr. ABC has booked 5 days out of his entitlement.
Syntax is =COUNTIF(range,criteria), so for example: COUNTIF(Sheet2!A2:L28,"x")
Where Sheet2 is one of the employees worksheets A2:L28 is the range & "x" is just the symbol I used in the above example to illustrate a way of representing the days holiday.
So if you place the letter 'x' in the holiday grid & then in sheet one, col B, place the countif formula, it should return the number of times an x has been placed in the grid - 5 x's returns a value of 5, showing that Mr. ABC has booked 5 days out of his entitlement.
Syntax is =COUNTIF(range,criteria), so for example: COUNTIF(Sheet2!A2:L28,"x")
Where Sheet2 is one of the employees worksheets A2:L28 is the range & "x" is just the symbol I used in the above example to illustrate a way of representing the days holiday.
