PDA

View Full Version : Solved: Creating a log file help required



dragon576
06-12-2007, 03:25 PM
Hi,
I have created a log file, which although untidy does the job. I want to refine it so that I can have some validation of the data.

Current solution.

First Sheet = Summary (Sheet 1)
Next 15 sheets are identical and contain the data (Report Sheets)

Summary Sheet
Sheet 1, Cell A1 displays the contents of Sheet 2 A1

Report Sheets
Userform populates Sheet n, cell A1 with a number ( Range("A1").Value = TxtProgress). This number represents hours

Cell A2 on each sheet combines number in A1 with date

=CONCATENATE("?"," ",TEXT(TODAY(),"dd/mm/yy")," ",A1,"

A macro then copies the date stamped entry into A3 as a running log (Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False)

E.g.
? 15/05/2007 4.5
? 08/05/2007 8
? 01/05/2007 5


This works, but if a user updates twice in a week, the value gets added to the log regardless of the previous entry. As there is also a running total value (sum of previous entries divided by working hours in day to give day value) it can quickly become inaccurate.

What I want to do now is as follows.

A dynamic table (range?) is created on each report sheet that increases/decreases in size as an entry is added/removed. Each entry needs a date entry (week number, or first monday of that week)

When a user inputs a value, the table is checked for an entry that week, and the user is asked if the previous value should replaced if an entry exisits for that week, otherwise the entry is just added to the table.

The table should be able to have a running total calculated (total number of hours so far) and copied into another cell (A4 = value/7.5 representing days used)

The end result would be.

1. Current value for that week in A1
2. Total value for all weeks in A4
3. A dynamic table/or cell showing all the previous entries with a date stamp (Previously A3).

Any help would be appreciated.

Many Thanks

Doug