Save & next

[Bobbykm]

Sh1 is a receipt (that I created in Excel) to be filled out by me with the
first receipt being #1. When filling out, the info is transferred via
formulas to their respected columns in Sh2 where I want to save the info. I
want to be able to change receipt to #2 but save the info in Sh2 from receipt
#1.

Thanks
Bobby

 

[FSt1]

hi.
I don't think you will be able to do what you want with formulas.
If i understand correctly, you have the formulas on sh2 drawing data from sh1.
are you trying to create a data base of receipts?
To do this with formulas, you would have to.....
1. put absolute references in the formula on sh2. (dollar signs in front of
the column number and row number.)
2. with each receipt, you would have to go to sh2, copy the fomulas down one
row then copy and paste the the receipt 1 row as values thus turning the
formulas into hard numbers.
3. go back to sh1 and enter recreipt 2.
4. repeat for each receipt.

yeah i know. labor intensive. but that's with formulas. it would work.
but i would recomend writing a macro to do this and reduce it to a one
button click.
unfortunately, you didn't provide enough info to suggest anything further.

regards
FSt1

 

[Bobbykm]

I appreciate your response and I was afraid someone would mention macros.
Not very good. Here is more details and Id appreciate your suggestions.
Im in Real Estate and collect rent.
Sheet1 is the following receipt when rent is paid (there is a lot more info
but not needed for example. ColH
R10 Date Received xx/xx/xx
R14 Amount paid
Tenant $ HAP $ Payment type
Total $ Check

Sheet2 is the Daily ledger where I have the data going.
ColC ColD
DATE NAME AMOUNT PAID PAYMENT TYPE
Tenant Housing
R4 04/23/07 - 6 7 Check
R5

I want to enter values in receipt, data being collected in Sheet2 then
clearing receipt for next entry which would go to next row in Sheet2. If
possible I would like to save receipt in another folder and have clean
receipt ready. I know I can save receipt manually but I was hoping to insert
some clickbutton to execute. Im probably asking to much so I understand if
you dont answer.


Thanks
Bobby

 

[Gord Dibben]

See Ron de Bruin's site for code to copy data to the next empty row on another
sheet.

http://www.rondebruin.nl/copy1.htm

Once that is done your macro can clear the cells in sheet1 ready for the next
set of entries.

Alternative is build a Template using the Template Wizard with Data Tracking.

To get the wizard add-in for newer versions of Excel see here for download.

http://support.microsoft.com/kb/873209/en-us


Gord Dibben MS Excel MVP