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
 

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,209
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,209
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,209
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.
 

Forum statistics

Threads
1,140,937
Messages
5,703,264
Members
421,289
Latest member
fbohlandt

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