Sum from several worksheets...

[saross]

Hello

I'm a totally newbie so please excuse me if this is a really stupi
question. My extent of excel so far is really poor but as I've done
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 staf
spend on different projects. There is a worksheet for each member o
staff where the time spent on a project is recorded on a weekly basi
(row titles for project; column headings = w/c date). I need to creat
a new worksheet that totals the amount spent by ALL STAFF on eac
project. I assume it will need to search the 'project' column of eac
worksheet and sum the values alongside it for each project?

I've started by creating a ValidContracts list so all entries ar
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 kin
of If Then Sum function that would search the Project Name column o
each worksheet and add up the adjacent values for each specifi
project?

Hope this makes some sort of sense!!

 

[Miguel Zapico]

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.

 

[Domenic]

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 D25.

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:A100"),E2,INDIRECT("'"&$D$
2:$D$5&"'!B2:B100")))

Hope this helps!