update sheet automatically

[balcardi_uk]

This is my example data, it shows employees and the quality of their work, this changes from day to day. But I need to record the quality of each employee for each day of the month. What I have been able to do create the formula below so that it stores the the quality of each employee for a particular day. eg. below shows the data for 21/01/05, of course when the date changes it will not be correct and I will have lost the old data aswell. Is it possible for the sheet to update, for each employee and for each day of the week and to record all info?
I hope this makes sense..I think i might of confused myself
Please help

I hope this is a lot simpler than it seems

Cheers
Balcardi

Sheet1 F1=21/01/05

Name Quality

David E13=20%
Tom 30%
Paul 33%
Kevin 12&

Sheet2

21/01/05 David
20%


=IF(Sheet1!F1=DATE(2005,1,21), Sheet1!E13)

 

[Fergus]

Hi Balcardi,

You can't do this with an IF statement, even if you put in the original cell reference if false, as then you'll get a circular reference.

Try this: Here's my sheet1

Updating.xls
	A	B	C	D	E	F
1	Name	Quality				5/5/05
2	David	5%				5
3	Tom	6%
4	Paul	7%
5	Kevin	8%
Sheet1

Here's Sheet2

Updating.xls
	A	B	C	D	E	F	G	H
1	Name	1/5/05	2/5/05	3/5/05	4/5/05	5/5/05	6/5/05	7/5/05
2	David					5%
3	Tom					6%
4	Paul					7%
5	Kevin					8%
Sheet2

Then put the following code in Sheet1

Option Explicit

Private Sub worksheet_Change(ByVal Target As Range)

If Target.Address = "$F$1" Then
    Application.EnableEvents = False

    Dim DayNo As Variant, rng As Range
    Dim Emp1, Emp2, Emp3, Emp4 As Variant

    Worksheets("Sheet1").Activate
    Application.ScreenUpdating = False

    Set rng = ActiveSheet.Range("F2,F2")
    DayNo = rng.Cells(1, 1).Value
    
    Set rng = ActiveSheet.Range("B2,B5")
    Emp1 = rng.Cells(1, 1).Value
    Emp2 = rng.Cells(2, 1).Value
    Emp3 = rng.Cells(3, 1).Value
    Emp4 = rng.Cells(4, 1).Value

    Worksheets("Sheet2").Activate
    Set rng = ActiveSheet.Range("B2,AF5")
    
    rng.Cells(1, DayNo).Value = Emp1
    rng.Cells(2, DayNo).Value = Emp2
    rng.Cells(3, DayNo).Value = Emp3
    rng.Cells(4, DayNo).Value = Emp4

    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If

End Sub

This will work for each month, but when you move onto the next month it will start overwriting, so you need to printout or whatever at the end of the month

HTH

 

[balcardi_uk]

Thanks for prompt reply

Is there anyway to do this by not using code, and not for the data to overwrite, and to update automatically and start a new sheet for the next month?

 

[Fergus]

Hi Balcardi,

Yesss, but it's not very pretty. You can create a new sheet for each month and manually enter the dates across the top of the first row and your names down column A. Create as many sheets as you have months.

Assuming your Sheet1 is the same as my example above, subsequent sheets will look like:

Updating.xls
	A	B	C	D	E	F	G	H
1	Name	1/1/05	2/1/05	3/1/05	4/1/05	5/1/05	6/1/05	7/1/05
2	David	5%	9%	13%	17%	0%	0%	0%
3	Tom	6%	10%	14%	18%	0%	0%	0%
4	Paul	7%	11%	15%	19%	0%	0%	0%
5	Kevin	8%	12%	16%	20%	0%	0%	0%
Sheet3

Formula in B2 is =IF(Sheet1!$F$1=B$1,Sheet1!$B2,B2) which you can copy down and across.

To use this, which involves a circulaar reference, you must go to Tools > Options and select the Calculation Tab and check the Iteration box, I also suggest you change the number of iterations to 1.

Be sure to change the date in Sheet1 Cell F1 before updating the "Quality" figures, otherwise you'll over-write the previous days figures in your monthly figures sheet.

HTH