hours from job-sheet to time-sheet

We have a sheet titled Job-Sheet.  In column A1-A20 there's days of a week. 
In column B1-B20 there's names.  In column C1-C20 there are hours.



MON      Anderson, Josh      8

MON      Bailey, John          8

MON      Blakely, Brett        8

TUE       Eddings, Cindy      8

TUE       Floyd, Bill              8

TUE       Anderson, Josh      8

WED     Bailey, John          9

WED     Blakely, Brett        9

WED     Eddings, Cindy      9

THUR     Anderson, Josh      10

THUR     Bailey, John          10

FRI        Anderson, Josh      10

SAT       Anderson, Josh      9

SAT       Bailey, John          9

SAT       Blakely, Brett        9

SAT       Eddings, Cindy      9



We have a second sheet titled Time-Sheet.  Each employee has a timesheet 
made of rows in column E through K making up a week.



                                      MON                  TUE 
WED        THU            FRI       SAT

Anderson, Josh                   8                        8 
0              10              10          10



If an employee works on Monday we want those hours automatically listed 
under MON in the timesheet cells.  How would we show hours in the time-sheet 
based on the job-sheet?



I have written a similar question prior to this, but hopefully this one is 
clearer.  We presently wish to do this without the use of a Pivot Table.



Thank you,

Gary


0
allge (63)
5/8/2007 7:10:09 PM
excel 39879 articles. 2 followers. Follow

8 Replies
603 Views

Similar Articles

[PageSpeed] 26

I have a workbook with Sheet 1, and Sheet 2.

I put my cursor in Sheet 1, A1 and enter the equals sign.

I now use the mouse to select Sheet 2, and randomly select cell A6 and press 
enter.

The formula stored in Sheet 1 cell A1 now reads: =Sheet2!A6

If you update Sheet 2 cell A6, it will be reflected in Sheet 1 A1

"Gary" wrote:

> We have a sheet titled Job-Sheet.  In column A1-A20 there's days of a week. 
> In column B1-B20 there's names.  In column C1-C20 there are hours.
> 
> 
> 
> MON      Anderson, Josh      8
> 
> MON      Bailey, John          8
> 
> MON      Blakely, Brett        8
> 
> TUE       Eddings, Cindy      8
> 
> TUE       Floyd, Bill              8
> 
> TUE       Anderson, Josh      8
> 
> WED     Bailey, John          9
> 
> WED     Blakely, Brett        9
> 
> WED     Eddings, Cindy      9
> 
> THUR     Anderson, Josh      10
> 
> THUR     Bailey, John          10
> 
> FRI        Anderson, Josh      10
> 
> SAT       Anderson, Josh      9
> 
> SAT       Bailey, John          9
> 
> SAT       Blakely, Brett        9
> 
> SAT       Eddings, Cindy      9
> 
> 
> 
> We have a second sheet titled Time-Sheet.  Each employee has a timesheet 
> made of rows in column E through K making up a week.
> 
> 
> 
>                                       MON                  TUE 
> WED        THU            FRI       SAT
> 
> Anderson, Josh                   8                        8 
> 0              10              10          10
> 
> 
> 
> If an employee works on Monday we want those hours automatically listed 
> under MON in the timesheet cells.  How would we show hours in the time-sheet 
> based on the job-sheet?
> 
> 
> 
> I have written a similar question prior to this, but hopefully this one is 
> clearer.  We presently wish to do this without the use of a Pivot Table.
> 
> 
> 
> Thank you,
> 
> Gary
> 
> 
> 
0
Access101 (3)
5/8/2007 8:23:01 PM
I posted a link to a sample file that demonstrates this in your other post.

Biff

"Gary" <allge@cox.net> wrote in message 
news:eqRVAUakHHA.208@TK2MSFTNGP05.phx.gbl...
> We have a sheet titled Job-Sheet.  In column A1-A20 there's days of a 
> week. In column B1-B20 there's names.  In column C1-C20 there are hours.
>
>
>
> MON      Anderson, Josh      8
>
> MON      Bailey, John          8
>
> MON      Blakely, Brett        8
>
> TUE       Eddings, Cindy      8
>
> TUE       Floyd, Bill              8
>
> TUE       Anderson, Josh      8
>
> WED     Bailey, John          9
>
> WED     Blakely, Brett        9
>
> WED     Eddings, Cindy      9
>
> THUR     Anderson, Josh      10
>
> THUR     Bailey, John          10
>
> FRI        Anderson, Josh      10
>
> SAT       Anderson, Josh      9
>
> SAT       Bailey, John          9
>
> SAT       Blakely, Brett        9
>
> SAT       Eddings, Cindy      9
>
>
>
> We have a second sheet titled Time-Sheet.  Each employee has a timesheet 
> made of rows in column E through K making up a week.
>
>
>
>                                      MON                  TUE WED 
> THU            FRI       SAT
>
> Anderson, Josh                   8                        8 0 
> 10              10          10
>
>
>
> If an employee works on Monday we want those hours automatically listed 
> under MON in the timesheet cells.  How would we show hours in the 
> time-sheet based on the job-sheet?
>
>
>
> I have written a similar question prior to this, but hopefully this one is 
> clearer.  We presently wish to do this without the use of a Pivot Table.
>
>
>
> Thank you,
>
> Gary
>
> 


0
biffinpitt (3172)
5/8/2007 8:31:45 PM
Biff,

I did receive and applied your formula in an earlier post, and it worked 
perfect.  That case referred to a list of employees, miles and totaling the 
miles to the proper employee.
I attempted to use the same concept on this application without success.  It 
seemed similar but I got of track when limiting one employees hours to one 
specific day.  I see below that my time-sheet example did not translate well 
in the email, but if a formula worked it would return a result for 
"Anderson, Josh" as 8 hours in the Monday cell, 8 hours in the Tuesday cell, 
0 hours in the Wednesday cell, 10 hours in the Thursday cell, 10 hours in 
the Friday cell and 9 hours in the Saturday cell.  Each employee on the crew 
would have his/her own time-sheet with hours worked on a specific day being 
displayed in a cell under that day.

Thank you for any further consideration of my question.

Regards,
Gary


"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:%23lWrm$akHHA.3996@TK2MSFTNGP06.phx.gbl...
>I posted a link to a sample file that demonstrates this in your other post.
>
> Biff
>
> "Gary" <allge@cox.net> wrote in message 
> news:eqRVAUakHHA.208@TK2MSFTNGP05.phx.gbl...
>> We have a sheet titled Job-Sheet.  In column A1-A20 there's days of a 
>> week. In column B1-B20 there's names.  In column C1-C20 there are hours.
>>
>>
>>
>> MON      Anderson, Josh      8
>>
>> MON      Bailey, John          8
>>
>> MON      Blakely, Brett        8
>>
>> TUE       Eddings, Cindy      8
>>
>> TUE       Floyd, Bill              8
>>
>> TUE       Anderson, Josh      8
>>
>> WED     Bailey, John          9
>>
>> WED     Blakely, Brett        9
>>
>> WED     Eddings, Cindy      9
>>
>> THUR     Anderson, Josh      10
>>
>> THUR     Bailey, John          10
>>
>> FRI        Anderson, Josh      10
>>
>> SAT       Anderson, Josh      9
>>
>> SAT       Bailey, John          9
>>
>> SAT       Blakely, Brett        9
>>
>> SAT       Eddings, Cindy      9
>>
>>
>>
>> We have a second sheet titled Time-Sheet.  Each employee has a timesheet 
>> made of rows in column E through K making up a week.
>>
>>
>>
>>                                      MON                  TUE WED THU 
>> FRI       SAT
>>
>> Anderson, Josh                   8                        8 0 10 
>> 10          10
>>
>>
>>
>> If an employee works on Monday we want those hours automatically listed 
>> under MON in the timesheet cells.  How would we show hours in the 
>> time-sheet based on the job-sheet?
>>
>>
>>
>> I have written a similar question prior to this, but hopefully this one 
>> is clearer.  We presently wish to do this without the use of a Pivot 
>> Table.
>>
>>
>>
>> Thank you,
>>
>> Gary
>>
>>
>
> 


0
allge (63)
5/8/2007 9:08:28 PM
Ok, now I'm confused!

You said the sample worked which was based on hours worked (your posted 
sample) but you say the actual data was for total miles and now you want the 
same thing for hours worked but it doesn't work?

Biff

"Gary" <allge@cox.net> wrote in message 
news:e%232xZUbkHHA.2272@TK2MSFTNGP02.phx.gbl...
> Biff,
>
> I did receive and applied your formula in an earlier post, and it worked 
> perfect.  That case referred to a list of employees, miles and totaling 
> the miles to the proper employee.
> I attempted to use the same concept on this application without success. 
> It seemed similar but I got of track when limiting one employees hours to 
> one specific day.  I see below that my time-sheet example did not 
> translate well in the email, but if a formula worked it would return a 
> result for "Anderson, Josh" as 8 hours in the Monday cell, 8 hours in the 
> Tuesday cell, 0 hours in the Wednesday cell, 10 hours in the Thursday 
> cell, 10 hours in the Friday cell and 9 hours in the Saturday cell.  Each 
> employee on the crew would have his/her own time-sheet with hours worked 
> on a specific day being displayed in a cell under that day.
>
> Thank you for any further consideration of my question.
>
> Regards,
> Gary
>
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message 
> news:%23lWrm$akHHA.3996@TK2MSFTNGP06.phx.gbl...
>>I posted a link to a sample file that demonstrates this in your other 
>>post.
>>
>> Biff
>>
>> "Gary" <allge@cox.net> wrote in message 
>> news:eqRVAUakHHA.208@TK2MSFTNGP05.phx.gbl...
>>> We have a sheet titled Job-Sheet.  In column A1-A20 there's days of a 
>>> week. In column B1-B20 there's names.  In column C1-C20 there are hours.
>>>
>>>
>>>
>>> MON      Anderson, Josh      8
>>>
>>> MON      Bailey, John          8
>>>
>>> MON      Blakely, Brett        8
>>>
>>> TUE       Eddings, Cindy      8
>>>
>>> TUE       Floyd, Bill              8
>>>
>>> TUE       Anderson, Josh      8
>>>
>>> WED     Bailey, John          9
>>>
>>> WED     Blakely, Brett        9
>>>
>>> WED     Eddings, Cindy      9
>>>
>>> THUR     Anderson, Josh      10
>>>
>>> THUR     Bailey, John          10
>>>
>>> FRI        Anderson, Josh      10
>>>
>>> SAT       Anderson, Josh      9
>>>
>>> SAT       Bailey, John          9
>>>
>>> SAT       Blakely, Brett        9
>>>
>>> SAT       Eddings, Cindy      9
>>>
>>>
>>>
>>> We have a second sheet titled Time-Sheet.  Each employee has a timesheet 
>>> made of rows in column E through K making up a week.
>>>
>>>
>>>
>>>                                      MON                  TUE WED THU 
>>> FRI       SAT
>>>
>>> Anderson, Josh                   8                        8 0 10 10 
>>> 10
>>>
>>>
>>>
>>> If an employee works on Monday we want those hours automatically listed 
>>> under MON in the timesheet cells.  How would we show hours in the 
>>> time-sheet based on the job-sheet?
>>>
>>>
>>>
>>> I have written a similar question prior to this, but hopefully this one 
>>> is clearer.  We presently wish to do this without the use of a Pivot 
>>> Table.
>>>
>>>
>>>
>>> Thank you,
>>>
>>> Gary
>>>
>>>
>>
>>
>
> 


0
biffinpitt (3172)
5/8/2007 9:27:00 PM
Bif,

I'm sorry for the confusion.  In short the two cases are all together 
different.  The answer to the first question you responded to does not fit 
this second and different question.  I appreciate your efforts but to avoid 
additional confusion, will look otherwise for an answer.  Thank you much for 
the time.

Regards,
Gary

"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:uTzoeebkHHA.1216@TK2MSFTNGP03.phx.gbl...
> Ok, now I'm confused!
>
> You said the sample worked which was based on hours worked (your posted 
> sample) but you say the actual data was for total miles and now you want 
> the same thing for hours worked but it doesn't work?
>
> Biff
>
> "Gary" <allge@cox.net> wrote in message 
> news:e%232xZUbkHHA.2272@TK2MSFTNGP02.phx.gbl...
>> Biff,
>>
>> I did receive and applied your formula in an earlier post, and it worked 
>> perfect.  That case referred to a list of employees, miles and totaling 
>> the miles to the proper employee.
>> I attempted to use the same concept on this application without success. 
>> It seemed similar but I got of track when limiting one employees hours to 
>> one specific day.  I see below that my time-sheet example did not 
>> translate well in the email, but if a formula worked it would return a 
>> result for "Anderson, Josh" as 8 hours in the Monday cell, 8 hours in the 
>> Tuesday cell, 0 hours in the Wednesday cell, 10 hours in the Thursday 
>> cell, 10 hours in the Friday cell and 9 hours in the Saturday cell.  Each 
>> employee on the crew would have his/her own time-sheet with hours worked 
>> on a specific day being displayed in a cell under that day.
>>
>> Thank you for any further consideration of my question.
>>
>> Regards,
>> Gary
>>
>>
>> "T. Valko" <biffinpitt@comcast.net> wrote in message 
>> news:%23lWrm$akHHA.3996@TK2MSFTNGP06.phx.gbl...
>>>I posted a link to a sample file that demonstrates this in your other 
>>>post.
>>>
>>> Biff
>>>
>>> "Gary" <allge@cox.net> wrote in message 
>>> news:eqRVAUakHHA.208@TK2MSFTNGP05.phx.gbl...
>>>> We have a sheet titled Job-Sheet.  In column A1-A20 there's days of a 
>>>> week. In column B1-B20 there's names.  In column C1-C20 there are 
>>>> hours.
>>>>
>>>>
>>>>
>>>> MON      Anderson, Josh      8
>>>>
>>>> MON      Bailey, John          8
>>>>
>>>> MON      Blakely, Brett        8
>>>>
>>>> TUE       Eddings, Cindy      8
>>>>
>>>> TUE       Floyd, Bill              8
>>>>
>>>> TUE       Anderson, Josh      8
>>>>
>>>> WED     Bailey, John          9
>>>>
>>>> WED     Blakely, Brett        9
>>>>
>>>> WED     Eddings, Cindy      9
>>>>
>>>> THUR     Anderson, Josh      10
>>>>
>>>> THUR     Bailey, John          10
>>>>
>>>> FRI        Anderson, Josh      10
>>>>
>>>> SAT       Anderson, Josh      9
>>>>
>>>> SAT       Bailey, John          9
>>>>
>>>> SAT       Blakely, Brett        9
>>>>
>>>> SAT       Eddings, Cindy      9
>>>>
>>>>
>>>>
>>>> We have a second sheet titled Time-Sheet.  Each employee has a 
>>>> timesheet made of rows in column E through K making up a week.
>>>>
>>>>
>>>>
>>>>                                      MON                  TUE WED THU 
>>>> FRI       SAT
>>>>
>>>> Anderson, Josh                   8                        8 0 10 10 10
>>>>
>>>>
>>>>
>>>> If an employee works on Monday we want those hours automatically listed 
>>>> under MON in the timesheet cells.  How would we show hours in the 
>>>> time-sheet based on the job-sheet?
>>>>
>>>>
>>>>
>>>> I have written a similar question prior to this, but hopefully this one 
>>>> is clearer.  We presently wish to do this without the use of a Pivot 
>>>> Table.
>>>>
>>>>
>>>>
>>>> Thank you,
>>>>
>>>> Gary
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
allge (63)
5/9/2007 2:09:42 AM
Ok, good luck!

Biff

"Gary" <allge@cox.net> wrote in message 
news:u5L1A9dkHHA.3472@TK2MSFTNGP04.phx.gbl...
> Bif,
>
> I'm sorry for the confusion.  In short the two cases are all together 
> different.  The answer to the first question you responded to does not fit 
> this second and different question.  I appreciate your efforts but to 
> avoid additional confusion, will look otherwise for an answer.  Thank you 
> much for the time.
>
> Regards,
> Gary
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message 
> news:uTzoeebkHHA.1216@TK2MSFTNGP03.phx.gbl...
>> Ok, now I'm confused!
>>
>> You said the sample worked which was based on hours worked (your posted 
>> sample) but you say the actual data was for total miles and now you want 
>> the same thing for hours worked but it doesn't work?
>>
>> Biff
>>
>> "Gary" <allge@cox.net> wrote in message 
>> news:e%232xZUbkHHA.2272@TK2MSFTNGP02.phx.gbl...
>>> Biff,
>>>
>>> I did receive and applied your formula in an earlier post, and it worked 
>>> perfect.  That case referred to a list of employees, miles and totaling 
>>> the miles to the proper employee.
>>> I attempted to use the same concept on this application without success. 
>>> It seemed similar but I got of track when limiting one employees hours 
>>> to one specific day.  I see below that my time-sheet example did not 
>>> translate well in the email, but if a formula worked it would return a 
>>> result for "Anderson, Josh" as 8 hours in the Monday cell, 8 hours in 
>>> the Tuesday cell, 0 hours in the Wednesday cell, 10 hours in the 
>>> Thursday cell, 10 hours in the Friday cell and 9 hours in the Saturday 
>>> cell.  Each employee on the crew would have his/her own time-sheet with 
>>> hours worked on a specific day being displayed in a cell under that day.
>>>
>>> Thank you for any further consideration of my question.
>>>
>>> Regards,
>>> Gary
>>>
>>>
>>> "T. Valko" <biffinpitt@comcast.net> wrote in message 
>>> news:%23lWrm$akHHA.3996@TK2MSFTNGP06.phx.gbl...
>>>>I posted a link to a sample file that demonstrates this in your other 
>>>>post.
>>>>
>>>> Biff
>>>>
>>>> "Gary" <allge@cox.net> wrote in message 
>>>> news:eqRVAUakHHA.208@TK2MSFTNGP05.phx.gbl...
>>>>> We have a sheet titled Job-Sheet.  In column A1-A20 there's days of a 
>>>>> week. In column B1-B20 there's names.  In column C1-C20 there are 
>>>>> hours.
>>>>>
>>>>>
>>>>>
>>>>> MON      Anderson, Josh      8
>>>>>
>>>>> MON      Bailey, John          8
>>>>>
>>>>> MON      Blakely, Brett        8
>>>>>
>>>>> TUE       Eddings, Cindy      8
>>>>>
>>>>> TUE       Floyd, Bill              8
>>>>>
>>>>> TUE       Anderson, Josh      8
>>>>>
>>>>> WED     Bailey, John          9
>>>>>
>>>>> WED     Blakely, Brett        9
>>>>>
>>>>> WED     Eddings, Cindy      9
>>>>>
>>>>> THUR     Anderson, Josh      10
>>>>>
>>>>> THUR     Bailey, John          10
>>>>>
>>>>> FRI        Anderson, Josh      10
>>>>>
>>>>> SAT       Anderson, Josh      9
>>>>>
>>>>> SAT       Bailey, John          9
>>>>>
>>>>> SAT       Blakely, Brett        9
>>>>>
>>>>> SAT       Eddings, Cindy      9
>>>>>
>>>>>
>>>>>
>>>>> We have a second sheet titled Time-Sheet.  Each employee has a 
>>>>> timesheet made of rows in column E through K making up a week.
>>>>>
>>>>>
>>>>>
>>>>>                                      MON                  TUE WED THU 
>>>>> FRI       SAT
>>>>>
>>>>> Anderson, Josh                   8                        8 0 10 10 10
>>>>>
>>>>>
>>>>>
>>>>> If an employee works on Monday we want those hours automatically 
>>>>> listed under MON in the timesheet cells.  How would we show hours in 
>>>>> the time-sheet based on the job-sheet?
>>>>>
>>>>>
>>>>>
>>>>> I have written a similar question prior to this, but hopefully this 
>>>>> one is clearer.  We presently wish to do this without the use of a 
>>>>> Pivot Table.
>>>>>
>>>>>
>>>>>
>>>>> Thank you,
>>>>>
>>>>> Gary
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
biffinpitt (3172)
5/9/2007 3:12:37 AM
On May 9, 12:10 am, "Gary" <a...@cox.net> wrote:
> We have a sheet titled Job-Sheet.  In column A1-A20 there's days of a week.
> In column B1-B20 there's names.  In column C1-C20 there are hours.
>
> MON      Anderson, Josh      8
>
> MON      Bailey, John          8
>
> MON      Blakely, Brett        8
>
> TUE       Eddings, Cindy      8
>
> TUE       Floyd, Bill              8
>
> TUE       Anderson, Josh      8
>
> WED     Bailey, John          9
>
> WED     Blakely, Brett        9
>
> WED     Eddings, Cindy      9
>
> THUR     Anderson, Josh      10
>
> THUR     Bailey, John          10
>
> FRI        Anderson, Josh      10
>
> SAT       Anderson, Josh      9
>
> SAT       Bailey, John          9
>
> SAT       Blakely, Brett        9
>
> SAT       Eddings, Cindy      9
>
> We have a second sheet titled Time-Sheet.  Each employee has a timesheet
> made of rows in column E through K making up a week.
>
>                                       MON                  TUE
> WED        THU            FRI       SAT
>
> Anderson, Josh                   8                        8
> 0              10              10          10
>
> If an employee works on Monday we want those hours automatically listed
> under MON in the timesheet cells.  How would we show hours in the time-sheet
> based on the job-sheet?
>
> I have written a similar question prior to this, but hopefully this one is
> clearer.  We presently wish to do this without the use of a Pivot Table.
>
> Thank you,
>
> Gary

Hi

I was thinking if this could be done the under-mentioned way:
Assumptions:

Range specified in the "Job Sheet" does not expand but is limited
within A1 - C20

For each Day there can be a maximum of 5 rows and a minimum 1 row

Then, in the second sheet, i.e. the Time-Sheet where u have the table
as follows

                                         Mon         Tue        Wed



0
5/9/2007 5:59:31 PM
Hi Gary

I was thinking if this could be done the following way, of course with
some assumptions:

The Assumptions are

For each day of the week, there can be maximum 4 rows, and minimum 1
row.

The data is input as per progressive days of the week, i.e. Mondays
come on top, then Tuesdays, then Wed....  (Or other wise u can add
another column, Give an ID no. corresponding to the day, and then sort
the Range)


Then, in the 2nd sheet, i.e. the Time sheet, where the table formation
is like
            Col D                    Col E    Col F   Col G   Col
H .........
Row 29                             Mon      Tue     Wed    Thu
Fri    Sat

Row 30: Anderson, Josh      XX       YY

Row 31Bailey, John             ZZ

Row 32 Blakely, Brett

Row 33  Eddings, Cindy

... . and so on....

Then for Cell XX the formula can be:

=IF(AND(E29=$A1,$D30=$B1),$C1,IF(AND(E29=$A2,$D30=$B2),$C2,IF(AND(E29=
$A3,$D30=$B3),$C3,IF(AND(E29=$A4,$D30=$B4),$C4,0))))

For Cell YY and subsequent cells on that row, copy the formula of Cell
XX and paste.

For Cell ZZ , the fomula will be similar to the formula of the
preceding row, albeit with minor changes

=IF(AND(E29=$A2,$D31=$B2),$C2,............. ,IF(AND(E29=$A8,$D31=$B8),
$C8,0))))))

... well a bit cumbersome I guess...

If this can help u in opening another channel of thought.. that would
be great.

Sanjib

0
5/9/2007 6:50:04 PM
Reply:

Similar Artilces:

How can I customize a report to give me account balances over time?
I am trying to create the following report and cannot figure out out to customize one of the reports available to give me what I need. Does anyone have an idea how I can do this? I want a report that lists all my accounts in the left column and months across the top header row. Then I want the account balance for that month in each row I've created an example of the report I want below (if it posts ok). For example: January February March Credit Card 1 1000 800 600 Credit Card 2 1500 1500 1400 Credit Card 3 3000 ...

type in one sheet and appear on all sheets
Hi, I have an excel spreadsheet that i use for work and it has 6 different pages. Some of the pages have the same information as the first page. How can i get the info from the first page to appear on all the other pages without typing it into every page? I have tried to do the = on the last page and carry it to the first page and press enter but on the last page all i get is #value. Could someone please tell me what i am doing wrong. thanks Jim. Click on the sheet tab of the first sheet, hold down the shift key. This groups the sheets together, anything typed on the first sheet w...

footer for chart sheets
Using Win 2K/Office 2K. Added this code to Personal.xls to create footer for each worksheet in workbook with file name and path. Public Sub PathAndFileNameInFooter() Dim wsSht As Worksheet For Each wsSht In ActiveWindow.SelectedSheets wsSht.PageSetup.LeftFooter = ActiveWorkbook.FullName Next wsSht End Sub It works great except I get a "type mismatch" error for chart sheets. How can I modify the code to also include chart sheets? -- maryj maryj, A chart sheet is not a worksheet, so you have to use a variable data type that includes both. ...

too long execution time and access violation error
Hello, Here is my problem. I first wrote a program for simplicity as a console application for image processing tasks. The program runs fine and gives correct result(a processed image) with an execution time of 30 microseconds. It has to run as MFC application. So i added few controls and a function to MFC and just copied my conole app code inside that new function. What happens now is that the same code that took 30 microsecond in console is now taking 55 seconds in MFC although in the end the correctly processed image is displayed. But then when i exit the application it raises access vio...

why doesn't sum add time
I have a starting time in cell A1 of 5:15, and in B1 I place :46, and I put sum of A1+B1 in column A3. I don't get 6:01, but instead I get #VALUE! What am I doing wrong? Hi have you entered your times really as time values?. What do the formulas =ISNUMBER(A1) and =ISNUMBER(B1) rreturn? -- Regards Frank Kabel Frankfurt, Germany "jvoortman" <jvoortman@canada.com> schrieb im Newsbeitrag news:460f01c4906a$30243970$a501280a@phx.gbl... > I have a starting time in cell A1 of 5:15, and in B1 I > place :46, and I put sum of A1+B1 in column A3. I don't > get 6:01, b...

Print Excel Comment in the same sheet
Dear All, I have a 5 paged Excel file and on 2nd page I have a comment which I want to be printed on the bottom of 2nd page. But when I set the page properties as "print at the end of sheet", the comment is printed on a seperate sheet... Is there anything to solve this issue Regards/Fuad ...

Create a List from Same Cell on Multiple Sheets
I am trying to create a list that comrised of the values of cell C3 on every worksheet. Is there a simple way to do this? Thanks in advance. Fritz Try Sub ddd() Dim wS As Worksheet Dim sTxt As String For Each wS In Worksheets sTxt$ = wS.Range("c3").Value Debug.Print sTxt Next End Su -- Message posted from http://www.ExcelForum.com Thanks. I guess it will take code. I'm pretty familiar with VBA but not so much with Excel's object model. Could you perhaps expand on the output portion within the For-Next Loop? I'd like to the values in a vertical list on a new ...

Average time...
I've got the cells formatted with "Custom" and m:ss. I'm using the formula: =AVERAGE(F10:F15) and it gives #DIV/0 Is there any way to keep the times in minutes:seconds format and still get the average? F10:F15 probably does not house true time values. You can check whethe this is so with... =ISNUMBER(F10) Kevin Wrote: > I've got the cells formatted with "Custom" and m:ss. > I'm using the formula: > =AVERAGE(F10:F15) > and it gives #DIV/0 > > Is there any way to keep the times in minutes:seconds format and still > get the average -...

Name a Sheet as Text in Sheet 1 "M2"
I was looking for someone who could help me with the following. I wanted to make a macro that at one point inserts a new sheet in the document and names it after the content of cell M2 in sheet 1. Thanks in advance! The best way for you to learn how to do this is to record a macro while you do it. -- Don Guillett SalesAid Software donaldb@281.com "Jasper" <Jasper@discussions.microsoft.com> wrote in message news:31CEA9EF-9144-4ADB-88B4-7F755F40E968@microsoft.com... > I was looking for someone who could help me with the following. I wanted to > make a macro that at one...

Approved Time Reporting
We have created the Project OLAP cube database and built the cubes in Office Project Server 2007. We do not use timesheets in our Office Project Server 2007 implementation. We enter and approve time on a project through the MyTask feature in EPM. We would like to create a report that shows the following: • Resource Name • Project Name • Task Name • Date Time Entered • Amount of Time Entered • Total Time Entered for Task Is it possible to create this report using Cubes without having timesheets enabled? If so, what Cube and what attributes can be used to report on the...

Deleting Sheet with VBA
Hi, I have this code that allows me to delete all sheet but the one i have selected: Sub DelSheet() For Each sheete In Sheets If ActiveSheet.Index <> sheete.Index Then Application.DisplayAlerts = False sheete.Delete Application.DisplayAlerts = True End If Next sheete End Sub Is there a way to change this code to allow me to delete all but the currently selected plus a sheet called "SS"? Thank you all, Bre-x One way: Option Explicit Sub DelSheet() Dim sheete As Worksheet For Each sheete In Worksheets If sheete.Name = ActiveSheet.Name _ Or L...

How do Icreate link from page of contents to sheets in workbook?
Hi see: http://www.mvps.org/dmcritchie/excel/buildtoc.htm -- Regards Frank Kabel Frankfurt, Germany "alaskapaf" <alaskapaf@discussions.microsoft.com> schrieb im Newsbeitrag news:84FBCFD6-54CA-4412-8C11-8707CBC307F6@microsoft.com... > ...

Does anyone have an Excel spread sheet for tracking 401 k account.
look in templates at the microsoft office site. -- Regards, Tom Ogilvy "timmaryan@msn.com" <timmaryan@msn.com@discussions.microsoft.com> wrote in message news:6F9B3BAA-B97E-4E4A-97BF-9063562CE07F@microsoft.com... > Specifically..... http://office.microsoft.com/en-us/results.aspx?Scope=TC&Query=401k Gord Dibben Excel MVP On Sun, 13 Feb 2005 17:42:53 -0500, "Tom Ogilvy" <twogilvy@msn.com> wrote: >look in templates at the microsoft office site. ...

In 3 active sheets in wkbk, determine& display the # of sheets that have data
I am wking with potentially 3 active sheets in a wkbk i.e. anyone of them or all could have data or text entered in them at any time. This is an ever changing scenario as data is entered or deleted i.e. a sheet is filled and the next sheet receives the new data etc. Since I am able or only want to see one sheet at a time, it is necessary to see on the displayed sheet how many of the 3 sheets have data on them at anyone time and to display that # of sheets numerically on all 3 sheets as I move thru them. I guess most simply stated I would like to know when I am in sheet #1 whether 1sheet is...

Creating a time line
I am interested in creating a timeline of events. Is there a way to do this using Office 2000 software? look at micrrosoft project instead "melanie" <couchm02@mindspring.com> wrote in message news:003201c3d542$d97d4810$a401280a@phx.gbl... > I am interested in creating a timeline of events. Is there > a way to do this using Office 2000 software? ...

Protecting Sheets #2
I have a workbook which has about 120 worksheets. Is there any way to set the Worksheet Protection on all sheets at one time? I want all sheets set to allow only the selection of unlocked cells. Patrick, Try this: Public Sub ProtectSheet() For Each sht In Sheets sht.Protect Next sht End Sub -- HTH /Sune Patrick Simonds wrote: > I have a workbook which has about 120 worksheets. Is there any way to set > the Worksheet Protection on all sheets at one time? I want all sheets set to > allow only the selection of unlocked cells. > > ...

Copy only certain lines from one sheet to another
Hi All Have a sheet that has data in. I would like to copy all lines from that sheet to next sheet if one column as a particular ' variable ' in i.e in this case L which represents a live case. To try and explain more, say for example sheet1 has 100 names and their adresses in with a final column with the letter L in. But only a selection has the letter L in. Would like to find these letter L and put them in sheet two, of course if sheet 1 has a letter L deleted or added sheet 2 is updated automatically. Hope I have explained ok. TIA p.s sorting sheet 1 and copying and past...

Remove time sheets that were not submitted
How do you remove time sheets that were not submitted ...

how to add hours and show actual hours not decimal numbers
I am trying to put together an employee work schedule, showing actual hours for punch in and out times. How do I set it up to add actual hours not decimal hours. Example: 11 hours is 0.46, if I change it to military time it will show 11 hours for the day but the total for the week is still all of the decimals added up. This is probably simple to do but I am too new with this. Any help would be greatly appreciated, Thanks B2: 23:00 start 11 PM C2 07:00 end 7 AM D2: =C2-B2+(C2<B2) format as time h:mm, or E2: =24 * (C2-B2+(C2<B2)) form...

Cannot open Excel Sheet direct from Outlook
When receiving Excel Sheet as an attachment, I have to save to disk before I can open it. What happens when you try? Is there an error message of any kind? What version of Outlook? "Modem_peddlar" <Modem_peddlar@discussions.microsoft.com> wrote in message news:9A2C98B4-40E7-4FFF-B9AB-069C32AFF1A2@microsoft.com... > When receiving Excel Sheet as an attachment, I have to save to disk before I > can open it. ...

Several files opening at the same time
I have a shortcut on my Quick Launch bar to a file called Index.xls wherein other hyperlinks take me to a zillion other places as a means of organisation of my computer data. For a couple of months I've been finding that whenever I click Index.xls another file called PERSONAL.XLS opens as well (or in fact momentarily before). I don't know why this is happening. As far as I know I did not change anything that might cause this phenomenon. I located PERSONAL.XLS and changed the filename to PERSONAL.XL1 to see if that might prevent it from opening, but now I have PERSONAL.XL1, PERSONA...

Macro for summarizing multiple sheets
Hello all I have a workbook with multiple sheets. Each sheet is identically laid out (the only difference is the sheet name - cart1, cart2, etc). Each sheet represent the contents of a cart and the expiration date of applicable items. I would like to create a macro or function that will search each sheet in sequence and return on a summary sheet the following information: the name of the sheet and any of the items with expiration dates that are within 45 day of the current date. Any help or suggestions would be much appreciated - see below for an example of the workbook ...

Data Calling from sheets not yet created
I have a workbook that has several sheets. Some sheets are created after data is entered into another sheet where I copy the sheet to a new sheet. The new sheet name is in date format (31-05-09) dd-mm-yy. I am trying to have one sheet grab info from the sheet created. Example this data collection sheet has dates in column A and I want to pull data from the sheet created with that date. Thank you in advance for your help. On Mon, 8 Jun 2009 08:12:28 -0400, "Ed Davis" <ed.davis1@verizon.net> wrote: >I have a workbook that has several sheets. Some sheets are created af...

Calculating Time 05-21-10
Hi I'm setting up a timesheet but when I enter the format to + or - if the 'time' is negative the figures are not showing - is there a way around this? Thanks guys Only by turning on the 1904 date system, which I wouldn't suggest :) -- Regards Dave Hawley www.ozgrid.com "Fie M-C" <FieMC@discussions.microsoft.com> wrote in message news:8071D879-166B-4F82-B949-76066EBA17EE@microsoft.com... > Hi > I'm setting up a timesheet but when I enter the format to + or - if the > 'time' is negative the figures are not showi...

Book with 365 sheets??
I thought I would create a workbook that contains 365 sheets (one sheet for each day of the week) that will contain rows of daily numerical data. However, if my workbook has 365 tabs, wouldn't that seem a bit too clunky or ineffecient??? Does anybody have any design ideas for a single workbook that would contain/operate on 365 sheets which could cover an entire year period??? Thank you! What exactly do you hope to gain by having 365 sheets that a one worksheet (or maybe 12) couldn't accomplish with some pivot tables? -- HTH, Barb Reinhardt "Robe...