update sheet automatically

balcardi_uk

New Member
Joined
Jan 19, 2005
Messages
4
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 :confused:
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)
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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
ABCDEF
1NameQuality5/5/05
2David5%5
3Tom6%
4Paul7%
5Kevin8%
Sheet1


Here's Sheet2
Updating.xls
ABCDEFGH
1Name1/5/052/5/053/5/054/5/055/5/056/5/057/5/05
2David5%
3Tom6%
4Paul7%
5Kevin8%
Sheet2


Then put the following code in Sheet1

Code:
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

New Member
Joined
Jan 19, 2005
Messages
4
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

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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
ABCDEFGH
1Name1/1/052/1/053/1/054/1/055/1/056/1/057/1/05
2David5%9%13%17%0%0%0%
3Tom6%10%14%18%0%0%0%
4Paul7%11%15%19%0%0%0%
5Kevin8%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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,550
Messages
5,636,962
Members
416,952
Latest member
prakashkumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top