1. Visiting from the US? Why not try DIYnot.US instead? Click here to continue to DIYnot.US.
    Dismiss Notice

Excel - archive to another sheet

Discussion in 'Software' started by jackpot, 1 May 2007.

  1. jackpot

    jackpot

    Joined:
    3 Nov 2005
    Messages:
    456
    Thanks Received:
    4
    Location:
    Liverpool
    Country:
    United Kingdom
    Anybody how i can transfer data from a worksheet in Excel into another sheet.

    For example
    I have one sheet that works out daily transactions/profits that automatically adjusts the total profit for that day after i enter each seperate transaction.

    I would like to have another worksheet that works out weeklt transactions simply by archiving the the total profit from the previous worksheet.

    So when i enter a transaction not only does the daily profit adjust but at the end of the day a can simpy click on the daily profit and this will archive/ transfer over to the other worksheet.

    Hope that makes sense and thanks in advance
     
  2. Sponsored Links
  3. WoodYouLike

    WoodYouLike

    Joined:
    14 Nov 2004
    Messages:
    7,710
    Thanks Received:
    215
    Location:
    Kent
    Country:
    United Kingdom
    Open both worksheets.
    In the worksheet you want the 'answer' on, enter a normally function (e.g. add all highlighted cells) then highlight the cells for the sum on the other worksheet.
    Is in fact 'as simple' as adding something together on one worksheet, only the data comes from another worksheet.

    Hope this makes sense?
     
  4. Diyisfun

    Diyisfun

    Joined:
    15 Jun 2004
    Messages:
    7,238
    Thanks Received:
    282
    Location:
    Norwich
    Country:
    United Kingdom
  5. jackpot

    jackpot

    Joined:
    3 Nov 2005
    Messages:
    456
    Thanks Received:
    4
    Location:
    Liverpool
    Country:
    United Kingdom
    Think i am being misunderstood.

    I want to use the calculator in worksheet 1 on a reguler basis. There is some complex calculations that reach the amount i want.

    The figures i put in and the total i input manually along with the date, clients name and any comment.

    Once i have done the calculation, i would like all figures entered to be transferred over into a proper spreadsheet but without the formula in the cells.

    Once i have done one transaction in the calculator i can just press a button. All data transfers over to the spreadsheet. Then i can start entering a whole different set of figure in the calculator for the next transaction.

    Maybe im not explaining properly.
     
  6. Diyisfun

    Diyisfun

    Joined:
    15 Jun 2004
    Messages:
    7,238
    Thanks Received:
    282
    Location:
    Norwich
    Country:
    United Kingdom
    Dont know what you mean 'proper spread sheet', You can copy from Excel, by doing copy paste, you are given options then, select 'paste special', then select from there..
    If you think this is not what you want you could email me, if I dont know I will give you a web site where the people are the dogs b*****cks.
     
  7. empip

    empip

    Joined:
    24 Sep 2005
    Messages:
    6,313
    Thanks Received:
    171
    Country:
    United Kingdom
    Select and 'copy' the data for transfer.

    Move to, and highlight the location of the top left cell to be pasted into on the new sheet, use the 'Paste values' toolbar button or via 'Edit', 'Paste Special', 'Values'.

    Thus text and numeric values - not formulae - are pasted into the new location.
    A little formatting may be necessary but essentially you would have the required snapshot of the calculator area plus what ever else was selected.

    A macro could be useful but not essential.

    Within the new sheet, holding several snapshots of the calculator with data lined up in columns, uniformly.. One could sum non / consecutive values by using something like :-
    {=SUM(IF((A4:A1000)="Daily",(C4:C1000),0))}
    This is termed an 'Array formula' entered like this :-

    Type the array formula (as above) - do not type in the curly brackets.
    Press CTRL+SHIFT+ENTER. This inserts the formula as an array type note the automatic enclosing within curly brackets {} - not the same as merely pressing the bracket keys at the keyboard.

    The above formula looks down column A from, in the above case, a4 to a1000, when the exact word (no spaces) 'Daily' is encountered the cell value in column C same row as the word 'Daily' is added to a sum of other qualifying cells in column C

    You should be looking at how to 'name' areas of a spreadsheet, allows literally two button selection of a defined area (your calculator - plus extra data, See just above column 'A' - 'Name Box' ) then copy and paste-special values as above, with the array formula keeping score in a single cell on the new sheet.

    There is a raft of useful stuff hidden under 'edit' 'GoTo' 'Special' one can learn how to select non consecutive data then paste it consecutively.. etc etc.

    Ace site -- http://www.cpearson.com/excel/array.htm
    :cool:
     
  8. Sponsored Links
Loading...

Share This Page