Excel in accounting help

Angel.pie001

New Member
Joined
Jun 25, 2012
Messages
14

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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
829
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)
 

Angel.pie001

New Member
Joined
Jun 25, 2012
Messages
14
No Bro Didn't worked. I will show u how i want this to be

2d0kmy0.jpg
 

Angel.pie001

New Member
Joined
Jun 25, 2012
Messages
14

ADVERTISEMENT

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

madforgolf

Board Regular
Joined
Oct 20, 2006
Messages
177
Office Version
  1. 365
  2. 2016
Put on a filter for the column then copy and paste the info to the other page as you need it.
 

Angel.pie001

New Member
Joined
Jun 25, 2012
Messages
14

ADVERTISEMENT

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

Active Member
Joined
Jan 16, 2010
Messages
436
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

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

<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

*AB
1Accountcanteen
21502/01/12
33502/02/12
45002/04/12

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

Spreadsheet Formulas
CellFormula
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

Board Regular
Joined
Oct 20, 2006
Messages
177
Office Version
  1. 365
  2. 2016
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,401
Messages
5,595,939
Members
414,034
Latest member
torreyanderson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top