Excel in accounting help

[Angel.pie001]

---

I was wondering if somebody could help over this

sheet1
A-----B
1)------01/07/12
50----canteen
80----bank

2)------02/07/12
70-----bank
30-----misc exp

3)------03/07/11
20------canteen
20------misc exp

So now what i need is excel to evalutae the canteen account from column"B"

And feed into another excel sheet by himself
for example

Sheet2(i will call it as a canteen account)

A----B
50----01/07/2012
20----03/072012


I mean execl to find the word canteen from column B,if found then match a value from column A, and feed it into another sheet with the date above it.
likewise i want to create a monthly so there will be arround 30 possibilities of this account appearing.



Thank you
help please

 

[Skybluekid]

Use this to look for the date

=IF(ROWS(G$5:G5)>$G$2,"",(INDEX($C$3:$C$8,SMALL(IF($E$3:$E$8=$G$3,ROW($E$3:$E$8)-ROW($E$3)+1),ROWS(G$5:G5))))) (Cntrl Shft Enter)

Use this to look for the code

=IF(ROWS(G$5:G5)>$G$2,"",(INDEX($C$3:$C$8,SMALL(IF($E$3:$E$8=$G$3,ROW($E$3:$E$8)-ROW($E$3)+1),ROWS(G$5:G5))))) (Cntrl Shft Enter)

 

[Skybluekid]

if you have any problems let me know

 

[Angel.pie001]

No Bro Didn't worked. I will show u how i want this to be

 

[Angel.pie001]

The appearance of canteen is random, it can be in any of the boxes.

 

[madforgolf]

Put on a filter for the column then copy and paste the info to the other page as you need it.

 

[Angel.pie001]

Put on a filter for the column then copy and paste the info to the other page as you need it.

But i want in different sheet and whenever i change the base data refrence data should change

 

[MikeWx]

Hi Angel.pie001,

Here is an array formula solution for your question, so press CNTL SHIFT ENTER after putting it in Sheet2 Cells A2 and B2, instead of just ENTER. I have included the data in Sheet1 only as far as Row 19, but you can extend it as needed. The name of the account is in Cell B2 of the second sheet. Change this if you want the entries for a different value, such as Box Office. Copy the formulas in Sheet2 down as far as you need them.

Sheet1

*	A	B	C
1	*	*	*
2	*	*	*
3	*	*	2/1/2012
4	*	15	canteen
5	*	25	internet
6	*	35	Box Office
7	*	*	*
8	*	*	2/2/2012
9	*	35	canteen
10	*	25	Misc Exp
11	*	*	*
12	*	*	2/3/2012
13	*	25	Misc Exp
14	*	25	Bank
15	*	*	*
16	*	*	2/4/2012
17	*	75	Box Office
18	*	50	canteen

<COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Sheet2

*	A	B
1	Account	canteen
2	15	02/01/12
3	35	02/02/12
4	50	02/04/12

<COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula A2 {=IFERROR(INDEX(Sheet1!$B$3:$B$19,SMALL(IF(Sheet1!$C$3:$C$19=$B$1,ROW($A$1:$A$17)),ROW(A1))),"")} B2 {=IFERROR(INDEX(Sheet1!$C$3:$C$19,VLOOKUP(SMALL(IF(Sheet1!$C$3:$C$19=$B$1,ROW($A$1:$A$17)),ROW(A1)),IF(ISNUMBER(Sheet1!$C$3:$C$19),ROW($A$1:$A$17)),1)),"")} <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



Mike

 

[madforgolf]

Hi guys,

Im intrested in this also so i followed the layout, entered with CSE but not getting anything to work. when looking into formula stage 1 =#N/A so not sure what i have different.
cheers
Marty

 

[Angel.pie001]

Thanks Bro. It worked. I am
very thank full to you