A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula to combine cells (A challenge!)



 
 
Thread Tools Display Modes
  #1  
Old January 12th, 2004, 12:29 AM
external usenet poster
 
Posts: n/a
Default Formula to combine cells (A challenge!)

Hi,

tried my best to find the answer through the HELP menu and
the knowlege base...but unsucessfull.

I have an expense tracking Excel file, with worksheets for
every months. Each worksheet has the same format.
Column A has a controlled list of expense type (Office
Supplies, Entertainment, Telehpone...etc). Column B has
the amount. Each row is an expense. Here is an example:

- Worksheet 1 (Jan 04)
COLUMN A COLUMN B
Telephone $245
Rent $500
Gaz $46
Office Exp. $456

- Worksheet 2 (Feb 04)
COLUMN A COLUMN B
Telephone $233
Rent $500
Gaz $12

etc....

I need to create a worksheet that resumes all the expenses
per category. That means that I need to write a formula
for each expense type that does the following, for
exmaple: Find in all the worksheets, in Column A a value
equal to "Telephone", get the value on the same row in
column B and calculate the total for this category only.

Therfore, on the first worksheet it will resume all my
expenses for year in each category.

I thank anyone in advance for his ingenious help !.

Regards.


  #2  
Old January 12th, 2004, 01:37 AM
Norman Harker
external usenet poster
 
Posts: n/a
Default Formula to combine cells (A challenge!)

Hi!

As long as the items are in the same cell in each sheet and there are
no non-standard sheets in between:

=SUM('first sheet name:last sheet name'!A1)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


  #3  
Old January 12th, 2004, 01:39 AM
Anders S
external usenet poster
 
Posts: n/a
Default Formula to combine cells (A challenge!)

Hi,

If the expense types are on the same rows in each monthly sheet, you can =
try
=3DSUM('First Sheet:Last Sheet'!B2)
in the summary sheet and fill down.
You will have to substitute "First Sheet" and "Last Sheet" with your =
actual worksheet names.

Otherwise I think you need a macro.

HTH
Anders Silven


skrev i meddelandet =
...
Hi,
=20
tried my best to find the answer through the HELP menu and=20
the knowlege base...but unsucessfull.
=20
I have an expense tracking Excel file, with worksheets for=20
every months. Each worksheet has the same format.=20
Column A has a controlled list of expense type (Office=20
Supplies, Entertainment, Telehpone...etc). Column B has
the amount. Each row is an expense. Here is an example:
=20
- Worksheet 1 (Jan 04)
COLUMN A COLUMN B
Telephone $245
Rent $500
Gaz $46
Office Exp. $456
=20
- Worksheet 2 (Feb 04)
COLUMN A COLUMN B
Telephone $233
Rent $500
Gaz $12
=20
etc....
=20
I need to create a worksheet that resumes all the expenses=20
per category. That means that I need to write a formula=20
for each expense type that does the following, for=20
exmaple: Find in all the worksheets, in Column A a value=20
equal to "Telephone", get the value on the same row in=20
column B and calculate the total for this category only.
=20
Therfore, on the first worksheet it will resume all my=20
expenses for year in each category. =20
=20
I thank anyone in advance for his ingenious help !.
=20
Regards.=20
=20

  #4  
Old January 12th, 2004, 02:21 AM
GT
external usenet poster
 
Posts: n/a
Default Formula to combine cells (A challenge!)

thanks for your help.
Actually your formula will combine all expenses category...I want to separate per category.
The total expenses for all worksheets in the TELEPHONE category.
The total expenses for all worksheets in the GAZ category.
etc...

I ahve the impression I need a macro.
  #5  
Old January 12th, 2004, 02:59 AM
Norman Harker
external usenet poster
 
Posts: n/a
Default Formula to combine cells (A challenge!)

Hi GT!

The cell that you should refer to is the cell that contains the amount
for the category.

If you have more than one amount in a category on the sheets then I'd
recommend setting up totals within each sheet for each category using
SUMIF. Then you can add the items in those summaries as long as your
totalling section has the same matrix throughout.

You might use a more complex formula or VBA approach, but this looks
like the simplest way. KISS.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.