Automatic copying of data from one sheet to two sheets

**Bellablu** · 2013-11-05, 08:25 AM

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.

**Hercules1946** · 2013-11-13, 03:52 PM

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

**Hercules1946** · 2013-11-15, 10:12 PM

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?

**Hercules1946** · 2013-11-17, 12:07 AM

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.

**Hercules1946** · 2013-11-17, 11:48 AM

Originally Posted by hercules1946

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 ************

**NoS** · 2013-11-17, 05:35 PM

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.

**Hercules1946** · 2013-11-17, 09:33 PM

Originally Posted by NoS

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!