is this possible on excel?

I use a spread sheet to do work schedual
12 work sheet 1 for each month
columes are total days for the whole month( i use 4 extra columes between
weeks for extra info.)
rows are name of staff
I want to add 1 more work sheet to keep track of vacation time
Is there a formula or way so it will automatically mark on the schedual the
vacation time as entered in the vacation work sheet on the proper month and
day?


0
bob7418 (8)
7/4/2004 3:07:10 PM
excel 39879 articles. 2 followers. Follow

5 Replies
598 Views

Similar Articles

[PageSpeed] 51

Maybe something along the lines of this set-up ..

Assume you have in Sheet1, in cols A to D,
data in row2 downwards, the set-up:

Name...........Reason....1st day of abs...Last day of abs
John Walt.......MC..........01-Jul-04..........01-Jul-04
Peter Logan.....V............02-Jul-04..........03-Jul-04

where details concerned for staff on vacation, on sick leave etc
(e.g. "V", "MC" etc in col B) are entered

Notes:
----------
a. The names in col A in Sheet1 are assumed *unique*,
i.e. appear maximum once only

b. For *single day* events, the same date has to be entered
in *both* cols C & D
(Like what was entered for "John Walt" in the sample above)

-------------
In Sheet2 (for month of Jul-04, say)
-------------
Suppose you have the typical set-up below, in cols A - col xx,
data in row2 downwards, where:

- Names are listed in col A, row2 down
(The names need not necessarily be in the same order as Sheet1)

- Row1 in cols B, C, D, etc contain all the *dates* for the entire month
in sequential order right across, viz.: 1-Jul-04, 2-Jul-04 .. 31-Jul-04
(Note: Your 4 extra cols in-between each week shouldn't be a problem
as long as the col headers for these in row1 doesn't contain any *dates*,
which might conflict)

Name...........1-Jul-04...2-Jul-04...3-Jul-04...etc
Peter Logan
John Walt
etc

Put in B2:

=IF(ISBLANK($A2),"",IF(ISNA(MATCH(TRIM($A2),Sheet1!$A:$A,0)),"Unmatched
Name",IF(OR(B$1<OFFSET(Sheet1!$A$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,2),B$1>
OFFSET(Sheet1!$A$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,3)),"",OFFSET(Sheet1!$A
$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,1))))

Copy B2 right across until the rightmost col (for the last day of the
month),
then copy down until the last row of data in col A

The desired results for each name in col A will be returned by the formulae,
viz. for the sample data in Sheet1, Sheet2 will return:

Name...........01-Jul-04 02-Jul-04 03-Jul-04
Peter Logan........................V..............V
John Walt..........MC

Blank cells in col A (if any) will return blanks, while
"Unmatched Name" will be returned for cases
where the names in col A do not match those in Sheet1's col A

TRIM() is used to improve robustness in matching the names,
to remove any inadvertent leading, trailing or extra in-between-words spaces
in the names entered in col A of Sheet2

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"DR, Bob" <bob@mail.com> wrote in message
news:y2VFc.955448$Pk3.470872@pd7tw1no...
> I use a spread sheet to do work schedual
> 12 work sheet 1 for each month
> columes are total days for the whole month( i use 4 extra columes between
> weeks for extra info.)
> rows are name of staff
> I want to add 1 more work sheet to keep track of vacation time
> Is there a formula or way so it will automatically mark on the schedual
the
> vacation time as entered in the vacation work sheet on the proper month
and
> day?
>
>


0
demechanik (4694)
7/5/2004 6:08:12 AM
thanks I'll give that a try.
"Max" <demechanik@yahoo.com> wrote in message
news:e9gu7ZlYEHA.3012@tk2msftngp13.phx.gbl...
> Maybe something along the lines of this set-up ..
>
> Assume you have in Sheet1, in cols A to D,
> data in row2 downwards, the set-up:
>
> Name...........Reason....1st day of abs...Last day of abs
> John Walt.......MC..........01-Jul-04..........01-Jul-04
> Peter Logan.....V............02-Jul-04..........03-Jul-04
>
> where details concerned for staff on vacation, on sick leave etc
> (e.g. "V", "MC" etc in col B) are entered
>
> Notes:
> ----------
> a. The names in col A in Sheet1 are assumed *unique*,
> i.e. appear maximum once only
>
> b. For *single day* events, the same date has to be entered
> in *both* cols C & D
> (Like what was entered for "John Walt" in the sample above)
>
> -------------
> In Sheet2 (for month of Jul-04, say)
> -------------
> Suppose you have the typical set-up below, in cols A - col xx,
> data in row2 downwards, where:
>
> - Names are listed in col A, row2 down
> (The names need not necessarily be in the same order as Sheet1)
>
> - Row1 in cols B, C, D, etc contain all the *dates* for the entire month
> in sequential order right across, viz.: 1-Jul-04, 2-Jul-04 .. 31-Jul-04
> (Note: Your 4 extra cols in-between each week shouldn't be a problem
> as long as the col headers for these in row1 doesn't contain any *dates*,
> which might conflict)
>
> Name...........1-Jul-04...2-Jul-04...3-Jul-04...etc
> Peter Logan
> John Walt
> etc
>
> Put in B2:
>
> =IF(ISBLANK($A2),"",IF(ISNA(MATCH(TRIM($A2),Sheet1!$A:$A,0)),"Unmatched
>
Name",IF(OR(B$1<OFFSET(Sheet1!$A$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,2),B$1>
>
OFFSET(Sheet1!$A$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,3)),"",OFFSET(Sheet1!$A
> $1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,1))))
>
> Copy B2 right across until the rightmost col (for the last day of the
> month),
> then copy down until the last row of data in col A
>
> The desired results for each name in col A will be returned by the
formulae,
> viz. for the sample data in Sheet1, Sheet2 will return:
>
> Name...........01-Jul-04 02-Jul-04 03-Jul-04
> Peter Logan........................V..............V
> John Walt..........MC
>
> Blank cells in col A (if any) will return blanks, while
> "Unmatched Name" will be returned for cases
> where the names in col A do not match those in Sheet1's col A
>
> TRIM() is used to improve robustness in matching the names,
> to remove any inadvertent leading, trailing or extra in-between-words
spaces
> in the names entered in col A of Sheet2
>
> --
> Rgds
> Max
> xl 97
> ---
> Please respond in thread
> xdemechanik <at>yahoo<dot>com
> ----
> "DR, Bob" <bob@mail.com> wrote in message
> news:y2VFc.955448$Pk3.470872@pd7tw1no...
> > I use a spread sheet to do work schedual
> > 12 work sheet 1 for each month
> > columes are total days for the whole month( i use 4 extra columes
between
> > weeks for extra info.)
> > rows are name of staff
> > I want to add 1 more work sheet to keep track of vacation time
> > Is there a formula or way so it will automatically mark on the schedual
> the
> > vacation time as entered in the vacation work sheet on the proper month
> and
> > day?
> >
> >
>
>


0
bob7418 (8)
7/5/2004 12:32:05 PM
Pleasure' Bob !
Hope it works for you ..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"DR, Bob" <bob@mail.com> wrote in message
news:9TbGc.28399$WB5.24364@pd7tw2no...
> thanks I'll give that a try.


0
demechanik (4694)
7/5/2004 12:48:00 PM
The formula will work to fit my exact needs but a modification s needed if
possible.
On sheet 1 I built it as your example.
>Assume you have in Sheet1, in cols A to D,
>data in row2 downwards, the set-up:
>Name...........Reason....1st day of abs...Last day of abs
>John Walt.......MC..........01-Jul-04..........01-Jul-04
>Peter Logan.....V............02-Jul-04..........03-Jul-04


I need to enter in row 2 for example  John Walt his first day off (reason in
Column B dates in columns C&D, 2nd day(s) off in row 2 reason Column E dates
columns F & G and keep repeating to meet the number of days off one my have
in a month. As I am useing this on Vacation days some of my staff have up to
6 weeks holidays which may be broken into several different days in a month.
ie may take off July 3 to 6, then july 15 to20 & July 29.


"Max" <demechanik@yahoo.com> wrote in message
news:%23os1U5oYEHA.716@TK2MSFTNGP11.phx.gbl...
> Pleasure' Bob !
> Hope it works for you ..
> --
> Rgds
> Max
> xl 97
> ---
> Please respond in thread
> xdemechanik <at>yahoo<dot>com
> ----
> "DR, Bob" <bob@mail.com> wrote in message
> news:9TbGc.28399$WB5.24364@pd7tw2no...
> > thanks I'll give that a try.
>
>


0
bob7418 (8)
7/6/2004 3:56:15 AM
Sorry, Bob .. guess I'm out of ideas to modify as posted, but
perhaps you might want to try this alternative approach instead:

Suppose we now have

In Sheet1 (for input of staff on vacation, sick, etc)
------------
Names listed in row1, B1 across
with dates listed in col A, A2 down

Date.........John Walt..Peter Logan..etc
01-Jul-04...V
02-Jul-04...................MC
03-Jul-04...V
04-Jul-04...V..............V
etc

where V = On vacation, MC = On sick leave, etc

In Sheet2
-------------
Assuming the same headers (dates*) in row1, B1 across
and names listed in col A, A2 down
*may include your extra in-between cols, as before

Name...........1-Jul-04...2-Jul-04...3-Jul-04...etc
Peter Logan
John Walt
etc

Put in B2:

=IF(OR(ISNA(MATCH(B$1,Sheet1!$A:$A,0)),ISNA(MATCH($A2,Sheet1!$1:$1,0))),"",O
FFSET(Sheet1!$A$1,MATCH(B$1,Sheet1!$A:$A,0)-1,MATCH($A2,Sheet1!$1:$1,0)-1))

Copy B2 across until the rightmost col
(for the last day of the month),
then copy down until the last row of data in col A

For a neat look, we'll suppress extraneous zeros
from showing in Sheet2 via clicking:
Tools > Options > View tab > Uncheck "Zero values" > OK

The above will now return:

Name...........01-Jul-04 02-Jul-04 03-Jul-04 04-Jul-04
Peter Logan........................MC........................V
John Walt..........V............................V.............V
etc

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"DR, Bob" <bob@mail.com> wrote in message
news:zppGc.966234$Pk3.117018@pd7tw1no...
> The formula will work to fit my exact needs but a modification s needed if
> possible.
> On sheet 1 I built it as your example.
> >Assume you have in Sheet1, in cols A to D,
> >data in row2 downwards, the set-up:
> >Name...........Reason....1st day of abs...Last day of abs
> >John Walt.......MC..........01-Jul-04..........01-Jul-04
> >Peter Logan.....V............02-Jul-04..........03-Jul-04
>
>
> I need to enter in row 2 for example  John Walt his first day off (reason
in
> Column B dates in columns C&D, 2nd day(s) off in row 2 reason Column E
dates
> columns F & G and keep repeating to meet the number of days off one my
have
> in a month. As I am useing this on Vacation days some of my staff have up
to
> 6 weeks holidays which may be broken into several different days in a
month.
> ie may take off July 3 to 6, then july 15 to20 & July 29.


0
demechanik (4694)
7/6/2004 8:50:23 AM
Reply:

Similar Artilces:

Is it possible
I would like to know is it possible to make million dollar in the internet, if you have any suggestions, write on my forumhttp://isitpossible.truemillion.comMark...

Excel 2002 calcs slower than Excel 97
I just upgraded from Excel 97 on NT (2.8Ghz w/1GB Ram) to Excel 2002 on XP (3.0Ghz w/1GB Ram). Excel 2002 now seems to calculate a LOT slower. It seems to happen more when I've imported data from an Access query. But even after I've broken all of the links to the access query the calculation now takes on average 10-14 minutes!!! This used to take less than 1 minute. I'm open to any ideas... lets hear your suggestions & questions!!! ...

Can I program Excel cells to change colour at a set date?
I want certain cells to change from yellow to green when a date contained within those cells is reached. Excel version 2003 Use a conditional format. For instane you might format as yellow, then set a conditional format based on the formula =TODAY()>=datecell to apply the green color. Jerry sunflowermurray wrote: > I want certain cells to change from yellow to green when a date contained > within those cells is reached. Excel version 2003 ...

How do insert page numbers in excel
how do i insert page numbers in excel as it appears in word? You create a footer. Use File>Page Setup>Header/Footer>Footer>Page 1 -- Regards, Fred Please reply to newsgroup, not e-mail "Timothy" <Timothy@discussions.microsoft.com> wrote in message news:F49714AA-F9B4-430C-9F07-759B944429BF@microsoft.com... > how do i insert page numbers in excel as it appears in word? ...

Personal Folders in OWA
Hello, I have just enabled OWA on our Exchange 2003 SP2 through ISA Server 2004. Is there any way to access personal folders in OWA? Thanks in advance. If by personal folders you mean .pst files, the answer is NO! Sorry. C C wrote: > Hello, > > I have just enabled OWA on our Exchange 2003 SP2 > through ISA Server 2004. Is there any way to > access personal folders in OWA? > > Thanks in advance. > > "C C" <someone@atsbcglobal.net> wrote: >I have just enabled OWA on our Exchange 2003 SP2 >through ISA Server 2004. Is there any way...

How do I create a dual axes column chart in Excel
Hi...I am trying to create a dual axes column chart in MS Excel 2003. But the moment I add the secondary axis the bars are getting overlapped. Alok, This example should help: http://www.pdbook.com/index.php/excel/two_column_series_with_two_axes/ -- Regards, John Mansfield http://www.pdbook.com "Alok" wrote: > Hi...I am trying to create a dual axes column chart in MS Excel 2003. But the > moment I add the secondary axis the bars are getting overlapped. ...

Excel asking for save with no changes #5
Dave Peterson Wrote: > And xl2002 added an option that allows you to have more control: > Edit|links|startup prompt button. > check the "don't display the alert and update links" This sounds like exactly what I'm looking for! Unfortunately, I' running an older version of Excel. I'll have to hit up my IT perso for an update. Thanks! -Tomm -- tommy2 ----------------------------------------------------------------------- tommy20's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=281 View this thread: http://www.excelforum.com/sh...

Downloading Statements Directly Not Possible
I have Money 2002 installed on an Win XP Pro SP1 machine. Something happened and I probably did it, that causes the statement downloads not to go directly to the account. Rather they want to be saved in a file and then opened into Money. I then have to delete the download. I have tried several things in order to fix this: 1. I verified the file associations of OFC, OFX were correct. They were. 2. Lowered all my security settings in IE. Even put banks into Trusted site zone. No go. 3. Made new accounts. Didn't help. 4. My IE cache is large (500MB) and regularly cleaned out. 5. I made s...

excel in browser
hi out there Could you tell me if there is any chance to use cmd button to print preview (i.e charts, sheets, ranges,.) when excel file is open with internet explorer. I've created excel application myself and just recently found out that my file is to be hosted externaly on one of virtual learning environments (VLE) sites. When I uploaded my file onto (VLE) and then open with browser cmd buttons with printpriview methods caused error message "Print preview method of chart,range,.. class failed". Also I used to read from text files to obtain some information and display ...

Excell
How can I data that is in multiple rows and combine it into one row? ...

VBA and Excel help
I'm not a newbie at this but it has been so long I feel lost like one :) I need to construct a routine for the following: I have created a travel voucher form that has a 1300+ list of data of virtually every city in the country, many with multiple entries such as: WILLIAMSBURG, VIRGINIA, 1 Apr through 31 Aug WILLIAMSBURG, VIRGINIA, 1 Sep through 31 Mar Using data validation (list) as an input method, for each day traveled (from 1 day to 4 weeks, where each day can be a different location), the user inputs the travel location by clicking on the data validation list box drop arrow in the...

is this possible? reload a form that is based on a query with new query that is defined in the form.
I have a basic form. The form is based on a query, so when I open the form, a pop comes up where I enter in what I want to query. So instead of 30,0000 records, I get 1-10 records. on the form, I have a text box. I want to be able to enter in what I want to query next and afterupdate have the recordset/form reload with that query instead of my original qeury. I figure this has to be done with VB. I am just starting to learn some VB but I am lost. Any info would be great, thank you. It does. You need to apply the new query or table name, or the sql string to the forms recordsource, then...

Sorting Excel Worksheets
I created a large workbook with approx. 40 worksheets. Can anyone tell me how to resort the worksheets. They are numerical so the sorting should be easy. well you can do i manually by click-dragging the tabs into oder, but that will take time. otherwise my wisdom can't help you. "Roseanne" wrote: > I created a large workbook with approx. 40 worksheets. Can anyone tell me > how to resort the worksheets. They are numerical so the sorting should be > easy. Roseanne Need VBA code for this. Chip Pearson has this. http://www.cpearson.com/excel/sortws.htm Gord...

Merging excel files
Hello everyone, I have one excel file for every day of the month since June 2003. I would like to create one file per month and merge the daily file into it...is there another way to do it other than copy/paste? Please help there is a lot of files what are the names of your files "Raul V" <pacificinet@aol.com> wrote in message news:0fa401c377e3$026dd5e0$a601280a@phx.gbl... > Hello everyone, I have one excel file for every day of the > month since June 2003. I would like to create one file per > month and merge the daily file into it...is there another > way t...

embedding Word files in cells of Excel 2003
(I am a beginner with Excel) Each month I send an email with an xls attachment. The xls contains cells which contain quotation reference numbers for Word documents. This is for ISO2000 documentation tracking. Now I also send seperate emails each with the Word quotation attached but I'm being told that when these emails are later opened the attachment is not always present. Is it possible for me to link the quotation to the cell so that after emailing the xls the recipient can open the Word quotation by clicking on the relevant cell? Beemer I'm not sure you could link the ...

Multiple Excel Charts to PowerPoint
OK, I think I have a question that will stump even the best of you! :) I have several worksheets that contain multiple charts. I want to copy the charts from Excel to PowerPoint with one chart to a slide. I can do so one at a time by copying and pasting them. The drawback is that it takes lots of time to do this because I'm working with 250+ charts. My question is if there is a way to quickly export the charts to PowerPoint so that each slide contains a different chart. Partway through this article you'll find programmatic approaches to that very problem: http://peltier...

Possible to detect changes to an XmlDocument object?
If I instantiate an XmlDocument object, the load a file through the Load method, is there a way, later on in execution, to easily determine if the XmlDocument object has changed from the original version loaded? I'd like to be able to check a condition to see if the document in memory has been editted (i.e., nodes added, attributes changed, etc.) so that my user can decide whether or not to commit changes to the disk file before moving to another file. Will I have to resort to re-loading the file, and comparing OuterXml properties? Gabe Gabe Covert wrote: > I'd like t...

I need an excel plug in to save workbooks in adobe acrobat format
I used to have this feature on the toolbar, but following a severe recent excel and outlook crash, it got wiped off !! There seems to be no way to restore this essential facility, of choosing to save excel workbooks in a pdf format instead of xls. DOes anyone know how to restore it? Hi Chris, Try-File, Print, then select Acrobat Distiller. It will then ask you where you want the .pdf file to be saved. Thats how it works on mine. Hope that helps "chris brown" wrote: > I used to have this feature on the toolbar, but following a severe recent > excel and outlook crash,...

Is it possible to compare a string and a field name
I have the following tables: Membership, Subscriptions, OfficeUseOnly. The Office Table contains a Year, Year To and From [financial year] and the other fields are the 5 membership types (regular, student etc) and these 5 fields contain the different amount each type of member has to pay. I want to create a form whereby on entering the financial year and the membership type the correct amount is automatically inserted into the 'Amount' field: FinancialYear - in current form = OfficeUseOnly SubsFinYear and then if MemberType = 'Student' find Student field ...

Excel 2002 SP3 CSV Format Cell 'Special' Leading Zero
Excel 2002 SP3, CSV File, Format Cell 'Special' for Zip Code Leading Zero does not work. Is there any way to maintain the Zip Code Leading Zero (New England States) in a CSV File? I have tried Format 'Special' and 'Text' and have found the only solution to be to place a leading ' with each zip code. This is not a very practical solution. Is this a configuration problem, a bug, or a limitation of the CSV file? Thanks, William Hi you may use an adjacent cell with a formula for this. e.g. enter in B1 =TEXT(A1,"0000") or something similar according...

pictures in various shapes
Is it possible to insert a picture and make it into a circle or an oval or various other shapes than just squares and rectangles? Thank you ahead of time. with a graphics editor -- Rob Giordano Microsoft MVP - FrontPage "Stephanie" <Stephanie@discussions.microsoft.com> wrote in message news:014D0D06-15F9-4D92-8D93-64FBD3A9CA32@microsoft.com... | Is it possible to insert a picture and make it into a circle or an oval or | various other shapes than just squares and rectangles? Thank you ahead of | time. | | Stephanie wrote: > Is it possible to insert a pict...

Is this possible
I want to make a list of the files in several different folders. Is it possible to somehow "Copy and paste" just the filenames into a list, in either Word or Excel or Wordperfect. I am just trying to sort the files in several different folders to weed out duplicates. There has got to be an easier way then typing them all or screen printing the lists and manually weeding them out. Any suggestions. Frustrated... Hans, Here is some code that will start in a directory and list all files in that and any sub directories. It uses recursion to go through all the subs. Just ch...

Is this Possible ?
Hi Have a newsletter document with text boxes that have no outlines but internal gradient colour fills. Is it possible that a macro could remove all colour fills in one hit just leaving the text in place ? many thanks Dim oShape As Shape For Each oShape In ActiveDocument.Shapes If oShape.Type = msoTextBox Then If oShape.Line.Visible = msoFalse Then oShape.Fill.Visible = msoFalse End If End If Next oShape should clear the fill from text boxes that are filled and have no border. -- <>>< ><<> ><&l...

10th on excel
thanks dave and elsie- it worked!!! Please stay with original thread by using Reply -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "wamotoo" <anonymous@discussions.microsoft.com> wrote in message news:31d801c4aee7$dcaf7060$a401280a@phx.gbl... > thanks dave and elsie- it worked!!! ...

recipient unable to open excel 2003 doc with excel 2000
When ever I create an excel doc useing MSO Excel 2003 and send it to my biz partner who uses excell 2000 he is unable to open it with out it being all garbled. How can I save these documents so that he can open them. People who also have MSO Excel 2003 have no problem opening the doc. Thanks for your help in advance. FMF DOC wrote: > When ever I create an excel doc useing MSO Excel 2003 and send it to my biz > partner who uses excell 2000 he is unable to open it with out it being all > garbled. > > How can I save these documents so that he can open them. > > Peop...