Kind of complicated, help please!

D

davesearle

I am writing a database for my gym, and I have a sign in sheet where i
displays all the member details when I type in the membership number
then displays what they have to pay. The total amount taken i
displayed at the top (using a simple SUM formula). And the date i
there also.

At the end of each day, however, I want to transfer the total amount o
money taken into a column in a different sheet labelled 'Review', an
then reset all the fields in the original 'Sign In' sheet. This much
can do myself, however my problem comes that as soon as the detail
have been reset then the money obviously goes back to 0 (as it should
but the review page also puts it back to 0.

Is there any way I can use the same 'Sign In' sheet for every differen
day or will I need to make 365 sheets each for signing in on differen
days?

Oh yeah and is there any limit to the number of sheets you can have i
excel? I am using Windows 98, and Office 98 I assume
 
T

Tom Ogilvy

Usually, transfering data that you want to retain is done with a macro.
Data that is produced by a formula is recalculated each time the sheet is
recalculated, so if the referenced data changes, the formula changes.

So it sounds like you need a macro to copy your data to the review sheet.

Look in Help under limits

you can have as many worksheets as you want subject to available memory. I
suspect 365 would be feasible.
 
D

davesearle

Ah but my problem with that is I cant transfer the data to the same par
of the review screen each time. For example on the 1st Jan 2004 the dat
needs to go onto the 1st cell on the review sheet, whereas for late
dates they need to be at different areas on the sheet, so a macr
wouldnt work unless I made a different one for each day of the yea
which would be a lot! (still probably better than making another 36
sheets!
 
R

Ron Rosenfeld

Ah but my problem with that is I cant transfer the data to the same part
of the review screen each time. For example on the 1st Jan 2004 the data
needs to go onto the 1st cell on the review sheet, whereas for later
dates they need to be at different areas on the sheet, so a macro
wouldnt work unless I made a different one for each day of the year
which would be a lot! (still probably better than making another 365
sheets!)
Click to expand...

Is there some consistent relationship between the day of the year and where the
results go on the Review Sheet?

For example, if each entry goes on the 'next' row, then it is trivial to have
your macro find the last used row; add 1 to it to find the first empty row, and
then copy the data from your signin sheet.

The macro could then clear the sign in sheet and you start anew.

A lot depends on how your sheets are set up.

The following assumes:

1. You enter your member numbers in a named range on the SignIn sheet.
2. The Date and totals are also in Named Ranges on that sheet called "Date"
and "Sum".

3. Formulas for member information and the amount they should pay are such
that if there is nothing in the Member Number field, they will display a blank.

If so, the following is a very simplified macro that will copy the relevant
data to the Review sheet into columns A and B, and then blank the Member
Numbers on the SignIn sheet.

=================================
Sub DailyReview()
Dim LastRow As Long

'Find first empty row in Review
LastRow = Sheets("review").[A65535].End(xlUp).Row + 1

'Copy date and sum from SignIn sheet
With Sheets("Review")
.Cells(LastRow, 1) = Range(Names("Date")).Value
.Cells(LastRow, 2) = Range(Names("Sum")).Value
End With

'Clear SignIn sheet
Range(Names("Member_Number")).ClearContents

End Sub
=============================

You will undoubtedly need to change this to accommodate your layout. In
addition, you may want to do something if you run this macro multiple times on
the same day. But it should give you some ideas.


--ron
 
Top