Save & next

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bobbykm - 16 Sep 2007 18:02 GMT

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

Reply to this Message

FSt1 - 17 Sep 2007 03:00 GMT

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

> 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
[quoted text clipped - 4 lines]
> Thanks
> Bobby

Reply to this Message

Bobbykm - 17 Sep 2007 03:28 GMT

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

> hi.
> I don't think you will be able to do what you want with formulas.
[quoted text clipped - 25 lines]
> > Thanks
> > Bobby

Reply to this Message

Gord Dibben - 17 Sep 2007 04:01 GMT

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

>I appreciate your response and I was afraid someone would mention macros.
>Not very good. Here is more details and Id appreciate your suggestions.
[quoted text clipped - 54 lines]
>> > Thanks
>> > Bobby

Reply to this Message