Results 1 to 8 of 8

Thread: Automatic copying of data from one sheet to two sheets

  1. #1

    Automatic copying of data from one sheet to two sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I have a workbook for various users with very limited excel skills. The workbook records incoming payments received by that area (Cash Book Tab) & depending as to whether they are a cash/chq transaction or an eftpos transaction I need them to automatically split into two different sheets so that they can be sent off to two different areas to be processed. I am currently working in Excel 2007. Can anybody offer assistance with this with a formula / macro / etc. If so it would be very much appreciated. I have attached the workbook so that you can see what I am trying to do.
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Hello, and welcome to the forum.
    How would you want to do this ? Do you want the information to appear in the separate sheets as soon as it is entered in the cashbook, or at some later time that you choose?
    When you send off the additional sheets, you will need to be confident that the cashbook details don't alter otherwise your systems will be compromised.
    If it was my choice, I would generate the extra sheets when I chose, and perhaps place a broad message on the cashbook page "********* Not to be Amended ***********,
    or better still protect it.
    How would you separate the Cashbook pages, daily, weekly ? typically how much history would be in one Workbook ?

    If you can answer these questions it will be easier to advise you

    HTH
    Last edited by Hercules1946; 2013-11-13 at 03:55 PM.

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Bellablu
    You might have found a solution, but Ive done some work on this. Ive a little more to do, so as we haven't heard from you, I was wondering if you still need our help?

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Hi
    Ive been looking at this problem and Ive encountered something I don't understand. The formulae Im using on Sheet 3 are to pull records from Sheet2 based on a Payment method of Cash or Cheque using FIND( to verify the entries in
    Col B of sheet2 (the "METH" range). To get more flexibility I created a UDF to check the payment methods (Fstr). Im using this in one cell (B5) on Sheet3. The others on Sheet three use the FIND function. The return value in B5 is
    wrong because there is a difference in the way the array filtering operates with the two functions and yet the data being passed looks exactly the same.
    THere are two arrays of 30 elements
    METH Array
    TRUE = Cell Value either "Cash" or "Cheque"
    FALSE if not as above
    Row Numbers Array {1 to 30}

    An IF statement matches the TRUE line numbers. The FALSE ones are ignored.

    Now - With the FIND function the matching is one for one between the two arrays, returning 30 elements, but line 1 is FALSE and so the SMALL function returns 2 ("Cheque") which is correct.
    With the Fstr UDF it seems to be matching every line with every element in the METH Array, so 1 is returned which is wrong. Bearing in mind that the data appears identical in Evaluate formula
    I cant understand whats causing this and how to correct it.
    I know I can just leave the FIND in there, but the UDF will provide more functionality, and I would like to understand this. Id appreciate any help.
    Attached Files Attached Files

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by hercules1946 View Post
    hi
    ive been looking at this problem and ive encountered something i don't understand. The formulae im using on sheet 3 are to pull records from sheet2 based on a payment method of cash or cheque using find( to verify the entries in
    col b of sheet2 (the "meth" range). To get more flexibility i created a udf to check the payment methods (fstr). Im using this in one cell (b5) on sheet3. The others on sheet three use the find function. The return value in b5 is
    wrong because there is a difference in the way the array filtering operates with the two functions and yet the data being passed looks exactly the same.
    There are two arrays of 30 elements
    meth array
    true = cell value either "cash" or "cheque"
    false if not as above
    row numbers array {1 to 30}

    an if statement matches the true line numbers. The false ones are ignored.

    Now - with the find function the matching is one for one between the two arrays, returning 30 elements, but line 1 is false and so the small function returns 2 ("cheque") which is correct.
    With the fstr udf it seems to be matching every line with every element in the meth array, so 1 is returned which is wrong. Bearing in mind that the data appears identical in evaluate formula
    i cant understand whats causing this and how to correct it.
    I know i can just leave the find in there, but the udf will provide more functionality, and i would like to understand this. Id appreciate any help.
    *********please ignore - posted as new thread ************

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    673
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Herc, you seem to be having a very one sided conversation here. Did you happen to see this thread?

    http://www.mrexcel.com/forum/excel-q...orksheets.html

    I think the OP had an accepted solution prior to your posting in this thread here.

    Think you and I are among the very few that read the rules regarding cross posting.

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NoS View Post
    Herc, you seem to be having a very one sided conversation here. Did you happen to see this thread?

    http://www.mrexcel.com/forum/excel-q...orksheets.html

    I think the OP had an accepted solution prior to your posting in this thread here.


    Think you and I are among the very few that read the rules regarding cross posting.
    Hello NoS
    No I wasnt aware of the othe post on Mr Excel, but I was still interested in developing a process that I can use elsewhere. I ran into trouble when I tried to use a UDF in my array formulae. Its all explained in a new post today, so hopefully
    Someone might be able to throw some light. I almost completed it, but I hate it when I can't get things to work!


  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Heres my solution anyway although its probably too late now.

    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •