Getting Excel to retain data from past spreadsheets...

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Hello,

We are attempting to create a spreadsheet that can track
attendance bonus hits automatically from a weekly time
card calculator that can generate a hit report.

The problem is that the time card calculator is a weekly
spreadsheet and the attendance bonus eligablity sheet is
a monthly sheet.

the calculator will report as follows:
A B
1|Employee| Hit? |
2| Joe | 1 |
3| Bob | |

The eligibily sheet (seperate file) will reference
the "Hit?" cells for each tech and needs to add up the
total to figure how many total hits have been generated
for the month.

The big hurdle is that the calculator sheet has new data
in B2:B3. So the eligibility sheet needs to retain data
from previous files.

I've played with referenceing specific dates, and saw
something about freezing data but haven't been able to
work. My VB guru also is at a bit of a loss for how to
do this.

Anyone have any ideas? (short of changing the format of
one of the sheets so they are either both weekly sheets
or they are both monthy.)
 
Without seeing any of your data, have you considered creating a macro that will
copy the data from the weekly sheet, and then prompt you to select a week number
of say 1 to 5, and then depending on which you choose, paste the values into the
relevant weeks slot on the monthly sheet.

You can set up range names to make it easier to read. You can use InputBox to
allow the user to specify the week, amd Select case to determine what to do
depending on what answer is given, eg:-

Sub UpdateValues()

Dim ans As String

ans = InputBox("What Week is this data - 1,2,3,4 or 5?")
If ans = "" Then Exit Sub

Select Case ans

Case 1
Range("Week1").Value = Range("WeeklyData").Value
Case 2
Range("Week2").Value = Range("WeeklyData").Value
Case 3
Range("Week3").Value = Range("WeeklyData").Value
Case 4
Range("Week4").Value = Range("WeeklyData").Value
Case 5
Range("Week5").Value = Range("WeeklyData").Value

Case Else
MsgBox "That Week number is not correct - Try again!"
Call UpdateValues
End Select

End Sub
 
Back
Top