Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
saross
 
Posts: n/a
Default Sum from several worksheets...


Hello

I'm a totally newbie so please excuse me if this is a really stupid
question. My extent of excel so far is really poor but as I've done a
bit of Access programming before I've been landed with this project!!!
Some help and advice would be really gratefully appreciated!

I have to create a worksheet that records the amount of time staff
spend on different projects. There is a worksheet for each member of
staff where the time spent on a project is recorded on a weekly basis
(row titles for project; column headings = w/c date). I need to create
a new worksheet that totals the amount spent by ALL STAFF on each
project. I assume it will need to search the 'project' column of each
worksheet and sum the values alongside it for each project?

I've started by creating a ValidContracts list so all entries are
identical (I'm very proud that I managed to work out how to do this!!!
sad I know!) but I just don't know what to do now. Is there some kind
of If Then Sum function that would search the Project Name column of
each worksheet and add up the adjacent values for each specific
project?

Hope this makes some sort of sense!!!


--
saross
------------------------------------------------------------------------
saross's Profile: http://www.excelforum.com/member.php...o&userid=34233
View this thread: http://www.excelforum.com/showthread...hreadid=539870

  #2   Report Post  
Posted to microsoft.public.excel.misc
Miguel Zapico
 
Posts: n/a
Default Sum from several worksheets...

Hi,

You may want to take a look at the SUMIF function in the Excel help, that
may fit your needs. Also, for the consolidating process the function
INDIRECT may also help, as you can store the workbook/worksheet names in
cells instead of hardcoding formulas.

If you need help beyond the help file, you may post again with an example.

Miguel.

"saross" wrote:


Hello

I'm a totally newbie so please excuse me if this is a really stupid
question. My extent of excel so far is really poor but as I've done a
bit of Access programming before I've been landed with this project!!!
Some help and advice would be really gratefully appreciated!

I have to create a worksheet that records the amount of time staff
spend on different projects. There is a worksheet for each member of
staff where the time spent on a project is recorded on a weekly basis
(row titles for project; column headings = w/c date). I need to create
a new worksheet that totals the amount spent by ALL STAFF on each
project. I assume it will need to search the 'project' column of each
worksheet and sum the values alongside it for each project?

I've started by creating a ValidContracts list so all entries are
identical (I'm very proud that I managed to work out how to do this!!!
sad I know!) but I just don't know what to do now. Is there some kind
of If Then Sum function that would search the Project Name column of
each worksheet and add up the adjacent values for each specific
project?

Hope this makes some sort of sense!!!


--
saross
------------------------------------------------------------------------
saross's Profile: http://www.excelforum.com/member.php...o&userid=34233
View this thread: http://www.excelforum.com/showthread...hreadid=539870


  #3   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default Sum from several worksheets...

Assuming that for each sheet Column A contains the project name, and
Column B contains the corresponding values, try the following...

List the sheet names in a range of cells, let's say D2:D5.

List the project names in another range of cells, let's say Column E,
starting at E2.

Then...

F2, copied down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$D$2:$D$5&"'!A2:A10 0"),E2,INDIRECT("'"&$D$
2:$D$5&"'!B2:B100")))

Hope this helps!

In article ,
saross wrote:

Hello

I'm a totally newbie so please excuse me if this is a really stupid
question. My extent of excel so far is really poor but as I've done a
bit of Access programming before I've been landed with this project!!!
Some help and advice would be really gratefully appreciated!

I have to create a worksheet that records the amount of time staff
spend on different projects. There is a worksheet for each member of
staff where the time spent on a project is recorded on a weekly basis
(row titles for project; column headings = w/c date). I need to create
a new worksheet that totals the amount spent by ALL STAFF on each
project. I assume it will need to search the 'project' column of each
worksheet and sum the values alongside it for each project?

I've started by creating a ValidContracts list so all entries are
identical (I'm very proud that I managed to work out how to do this!!!
sad I know!) but I just don't know what to do now. Is there some kind
of If Then Sum function that would search the Project Name column of
each worksheet and add up the adjacent values for each specific
project?

Hope this makes some sort of sense!!!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Worksheets from one Workbook to Another halem2 Excel Worksheet Functions 3 March 25th 06 07:04 AM
Adding same cells across multiple worksheets LACA Excel Worksheet Functions 2 January 19th 06 04:21 PM
Worksheets moved or deleted by unknown means! Dan Excel Discussion (Misc queries) 1 December 5th 05 10:52 PM
Changing a Link Mid-way Across Worksheets Frosty Excel Worksheet Functions 0 August 25th 05 01:03 AM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM


All times are GMT +1. The time now is 05:19 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"