I don't know what formula to use.

omard.

New Member
Joined
Mar 15, 2006
Messages
16
I have made a couple of searches, but since I don't know exactly what formula to use I'm a little lost when I do my search. I have a list that I will add to as time goes on. It will have peoples names, banks names, dollar amounts, company name, etc. I have to pull the names from the main sheet and put them on other sheets. Example, on my main sheet I have


a b c d
1 name bank amount source
2 john abc 120 adl
3
4
5

this list will have other names, banks, amounts, sources, and What I need to do is take all of the same names and add up their dollar amounts and place them in another sheet. I also need to be able to populate the name from this sheet to the other sheet. example if I add matt I want it to automatically insert that name into the other sheet and add up all his amounts. Sorry if I'm doing a horrible job of explaining. Ask anything you want. Thanks
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
excel 2000, but i can use 2003 if that will help

Excel 2003 simplifies the needed set up a good deal...
Book1
ABCDEFGH
103
2namebankamountsourceidxnamesubtotal
3johnabc120adl1john245
4joeamro230adl2joe230
5johncity bank125dfl carl510
6carlcity bank210dfl3  
7carlfirst national300gdl 
8Total985
9
Sheet1


Select A2:E7.
Run Data|List|create List.

E1 must house a 0.

E3, copied down:

=IF(ISNUMBER(MATCH(A3,$A$2:A2,0)),"",LOOKUP(9.99999999999999E+307,$E$1:E2)+1)

G1:

=LOOKUP(9.99999999999999E+307,E1:E7)

G3, copied down:

=IF(ROWS($G$3:G3)<=$G$1,LOOKUP(ROWS($G$3:G3),$E$3:$E$7,$A$3:$A$7),"")

H3, copied down:

=IF(G3<>"",SUMIF($A$3:$A$7,G3,$C$3:$C$7),"")

When the data area expands by new additions, all formulas will adjust automatically their range refernces.
 

omard.

New Member
Joined
Mar 15, 2006
Messages
16

ADVERTISEMENT

Thank you very much, but I need the g,h part to be on another sheet.
 

omard.

New Member
Joined
Mar 15, 2006
Messages
16

ADVERTISEMENT

Thank you very much, but I need the g,h part to be on another sheet.

Just cut the formula area in G:H and paste it on the destination sheet.


sorry but I don't quite understand what you mean.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thank you very much, but I need the g,h part to be on another sheet.

Just cut the formula area in G:H and paste it on the destination sheet.


sorry but I don't quite understand what you mean.

1] Reproduce the exhibit I posted on Sheet1 in a workbook in order to have a feel of how it works.

2] Then Edit|Cut the area in G:H and Edit|Paste it on Sheet2.

What you then see should give you an idea of how to implement the set up for your actual data.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thank you very much, but I need the g,h part to be on another sheet.

Just cut the formula area in G:H and paste it on the destination sheet.


sorry but I don't quite understand what you mean.

1] Reproduce the exhibit I posted on Sheet1 in a workbook in order to have a feel of how it works.

2] Then Edit|Cut the area in G:H and Edit|Paste it on Sheet2.

What you then see should give you an idea of how to implement the set up for your actual data.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,916
Messages
5,545,022
Members
410,647
Latest member
bernardazar
Top