Auto Splitting of Data to 2 Worksheets

[Bellablu]

Hi,

I am creating a workbook for other users to manage money receipting. Problem is receipted data it is processed by two different areas & the data needs to be split & sent respectivly. I am trying to make it simple for the areas receiving the monies as this is not their primary job I am dealing with numerous staff with different skill sets & knowledge.

I have tried to attach my workbook & images but I am not having any luck.

Basically I want the receivers to complete sheet one only of the Cash Book.

I am now trying to work a macro / formula / whatever to auto popuate the further two sheets isolating the Cash/Cheque & EFTPOS payments, which in turn can be just printed & sent off to their respective areas.

Can anyone offer assistance or a solution.

Much appreciated.

 

[bschwartz]

Hi Bellablu,

The above formula is known as an array formula. For Excel to know that you are entering an array formula, you need to use Ctrl+Shift+Enter to confirm it instead of just enter.

1) Copy the formula.
2) Highlight the cell you want the formula in, and hit F2 on the keyboard to go into edit mode.
3) Paste the formula.
4) Hold Ctrl and Shift and hit Enter (while still holding Ctrl+Shift).

Let me know how it works.

 

[Bellablu]

Hi Ben ,

I am doing well, I can actually get the formula pasted into the cells ok now. Unfortunatly I am getted the responce "EFTPOS" in every cell, which part of the formula (if any) do I adjust to fix that, or am I not ment to past it into every cell accross the sheet. I know I am missing something, just not sure what it is.

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>	I</SPAN>
1</SPAN>	Transaction type:</SPAN>	EFTPOS</SPAN>
2</SPAN>
3</SPAN>	Payment Method</SPAN>	Date</SPAN>	Receipt #</SPAN>	Name</SPAN>	Amount Received (GST Inc)</SPAN>	Check</SPAN>	Permit</SPAN>	Permit</SPAN>	Licence</SPAN>
4</SPAN>	EFTPOS</SPAN>	EFTPOS</SPAN>	EFTPOS</SPAN>
5</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL span=4></COLGROUP>

 

[bschwartz]

I'm not sure. Can you copy and paste the formula that is in each of the cells, so I can troubleshoot?

A4:
B4:
C4:

 

[Bellablu]

Hi Ben,

I had another play with it.

I again pasted the formula in A4 as per the Shift+Ctl+Enter direction & then held the cell (A4) & dragged it along the row & it populated the required data in all the cells - win one for me yay

I then dragged down the 'A' column & then accross each of the rows & guess what, fabulous it worked & gave me all the correct data - win two for me yay

Now that I have it working, I want to understand the formula, especially if someone screws with it. So if its ok with you I may need to you to tell me what each bit of the formula works as I am working through it.

Thanks so much for your help it is so appreciated.

Cheers

Narene

 

[bschwartz]

See if this helps you understand:

Highline Excel Class 45: INDEX function & MATCH function 12 Unusual Examples - YouTube

In particular, see example at around 18:25.

 

[Bellablu]

Unfortunatly 'You Tube' is blocked at work , but I will watch it at home tonight.

I have the EFTPOS page all set up & it is working great, thank you so much for that.

I am trying to match the formula info the cells & sheets to understand each part of it. Beacuse the next sheet has two variables 'Cash' & 'Chq" to draw on.

How & where do I adjust the formula to go for the two.

 

[bschwartz]

You should be able to simply change the value in cell B1 on the EFTPOS tab to Cash, and the values will change. Thus is you want them on seperate worksheets, you can copy the worksheet, and just change the value in cell B1.

 

[Bellablu]

Hi Ben,

I have done that & it brings the cash transactions in fine. The problem being in that second sheet I need to bring both 'Cash' & 'Chq' transactions in, & when I put in the B1 cell 'cash' 'Chq' it causes a drama. So I need to adjust the formula, but I am unsure of which part to adjust.

Hope that make sense.

Narene

 

[bschwartz]

Ok, I misunderstood you before. Try putting the criteria in B1 and C1 (so Cash and Cheque, it doesn't matter which order). And use this formula in A4:

=IF(ROWS(A$3:A3)<=SUMPRODUCT(COUNTIF('Transaction Data'!$A$2:$A$999,$B$1:$C$1)),INDEX('Transaction Data'!A$2:A$999,SMALL(IF('Transaction Data'!$A$2:$A$999=$B$1:$C$1,ROW('Transaction Data'!$A$2:$A$999)-ROW('Transaction Data'!$A$2)+1),ROWS(A$4:A4))),"")

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Transaction type:	Cash	Cheque
2
3	Payment Method	Date	Receipt #	Name	Amount Received (GST Inc)	Check	Permit	Permit	Licence
4	Cheque	30/10/13	231	John Doe	40		40
5	Cash	31/10/13	233	Darren Loser	20				20
6	Cheque	41285	234	Brad Walks	44
7	Cash	41316	236	Jane Doe	80
8	Cheque	41316	237	Hanry Whinst	20
cashchq

Cell Formulas
Range	Formula
A4	{=IF(ROWS(A$3:A3)<=SUMPRODUCT(COUNTIF('Transaction Data'!$A$2:$A$999,$B$1:$C$1)),INDEX('Transaction Data'!A$2:A$999,SMALL(IF('Transaction Data'!$A$2:$A$999=$B$1:$C$1,ROW('Transaction Data'!$A$2:$A$999)-ROW('Transaction Data'!$A$2)+1),ROWS(A$4:A4))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Bellablu]

Hi Ben
Guess what it all works. Thank you for you help & patience with me doing this & constantly asking Q's. It is so very, very much appreciated

Narene