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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|