trying to create a totals sheet that will get data from one or many sheets

[John D. Inkster]

I have a work book that I use for a time sheet. It contains four
worksheets 1) "blank time card" 2) "employees 3) "job numbers" 4)
"totals"

What I am trying to do is take the value from range "b8 to b31" ( the
job numbers) and it's corresponding total times from "q8 to q31"&"r8
to r31" from ANY worksheet other than the ones named above (1-4) and
put the data on sheet "totals".

Now for the kicker... sheet "me" may contain one or more job numbers,
and the next sheet "you" may not have the same numbers or in the same
order.

Any help is greatfuly appreciated

John Inkster

 

[KC Rippstein]

If those sheets are always the first four worksheets, and if the fifth sheet
and the last sheet can always have the same name, then you can just use
SUMIF.
So if Sheet5 is named Blank and the final sheet is named Template, your
formula would be
=SUMIF(Blank:Template!$B$8:$B$31,JobNumbers!A1,Blank:Template!$Q$8:$R$31)
then copy that formula down as many rows as you need.

You could just never use the Blank worksheet and use the Template worksheet
as a template that you make a copy of for new hires.

- KC

 

[John D. Inkster]

On "blank time card" I have 2 vlookup references 1) for the employee
name on worksheet "employees" and 2) for the job number on worksheet
"job numbers". I use several macro's one of which makes a copy of the
worksheet "blank time card" and names it with the name of the
employee. I then clear the data on the "blank time card" and start
again.

So what I think I would need is a way to check each worksheet for the
job number, if it exists then add it to the totals sheet and the
corrisponding hours. Then go down to the next cell on the time card
check for the job number no the "totals" sheet, if it exists add the
hours if not add the job number to the totals sheet and so on.

I would also like to be able to do this at any time and not corrupt
the existing data, by this I mean to erase all the "old" numbers and
times and recalculate all the worksheets again, in the event I have to
go back and correct a time card. I don't want to add the same
worksheet again.

I'm not asking for much am I?
John

 

[KC Rippstein]

Okay, that is a bit clearer. I did not realize your data was going to be so
temporary. You're right that you will pretty much need a macro to do this.
You may want to post your request to the Excel programming newsgroup, as I
am unable to assist with VBA. There are others here who are kind enough to
post answers to VBA here, but your post may be getting old for others to
notice it now.
I think it will be fairly easy, though. It will probably have something
like "for each sht in workbook" and then use a case statement to skip the
first four sheets, then keep a running total for you of each total per job
number. That doesn't sound like a lot...I think you'll have an answer in no
time.

 

[John D. Inkster]

Thanks KC

Okay, that is a bit clearer. I did not realize your data was going to be so
temporary. You're right that you will pretty much need a macro to do this.
You may want to post your request to the Excel programming newsgroup, as I
am unable to assist with VBA. There are others here who are kind enough to
post answers to VBA here, but your post may be getting old for others to
notice it now.
I think it will be fairly easy, though. It will probably have something
like "for each sht in workbook" and then use a case statement to skip the
first four sheets, then keep a running total for you of each total per job
number. That doesn't sound like a lot...I think you'll have an answer in no
time.