Free Microsoft Excel 2013 Quick Reference

Excel as Attendance Tracker

Merry Christmas Everyone!!

I've posted this on another board - got lots of views but no responses
so I'm hoping Santa will be good to me and someone reading this thread
will have an answer.

We have 100+ employees. We need to track PTO, Vacation in hours and
then also log other instances of time away from work (f= fmla,
t=travel, c=comp time, h=work at home). However these "other
instances" do not need to be tracked in terms of hours used.

The only suggestion that came on the other board was to have 3 rows per
employee - not a good solution.

My solution so far has been to have 3 columns for each day but this is
causing me to run out of columns before I get to April.

I thought I could use SUMIF but apparently I'm wrong. Why can't I put
something like "8v" (indicating 8 hours of vacation used) and then have
my totals column look at the range and sum if it says 8v, the column
next to this would sum if it said 8p.

Any help would be appreciated.

--
Donna123
------------------------------------------------------------------------
Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962
View this thread: http://www.excelforum.com/showthread...hreadid=495940


Good Morning All,

Im very new to the VBA coding & Macros, I need a favour from You hope i will get help

I have an Excel sheet named as Attendance Tracker in which i maintain attendance of employees.

Now my seniors are asking me to automate the same & their requirements are -
we will create seperate sheets for all the team leadersEach team lead should access only their team sheet and should not have access to other sheetsWe should have consolidated sheet where in all team leads data has to be consolidated.consolidated sheet should be accessible only from managers.

Please help me regarding this.

Thanks & Regards
Tanveer

Merry Christmas Everyone!!

I've posted this on another board - got lots of views but no responses so I'm hoping Santa will be good to me and someone reading this thread will have an answer.

We have 100+ employees. We need to track PTO, Vacation in hours and then also log other instances of time away from work (f= fmla, t=travel, c=comp time, h=work at home). However these "other instances" do not need to be tracked in terms of hours used.

The only suggestion that came on the other board was to have 3 rows per employee - not a good solution.

My solution so far has been to have 3 columns for each day but this is causing me to run out of columns before I get to April.

I thought I could use SUMIF but apparently I'm wrong. Why can't I put something like "8v" (indicating 8 hours of vacation used) and then have my totals column look at the range and sum if it says 8v, the column next to this would sum if it said 8p.

Any help would be appreciated.

Hi everyone,

I have been tasked with coming up with an attendance tracker at work. I have been messing with it for a while now and I have been surfing the web for answers. I have found a lot of things that are similar, but I can't quite adapt them to what I want to do.

So, we award 1/3 of a point for a tardy and 1 point for an absence.

The Tardy/Absence criteria will pull from another spreadsheet.

I used a countif to determine the number of points based on Tardy/Absent.

I am now trying to create a rolling 90 day total and a rolling 365 day total. So I will pull up the file, add a new column that will pull the previous day's Absences/Tardies and then have my 90/365 day period update to reflect the new date.

I have been messing around with OFFSET and EDATE, which are new to me. If anyone can help me come up with a formula to figure this out, I would be ecstatic! It would be even better if you could explain how it was done as well so I can learn. Thanks!

~B

Hi,

I have attached a sample tracker - where i have 2 tabs one is attendance tracker and another one is production tracker.

Now i am looking out for a formula which counts attendance based on there production numbers for that respective day.

Here is what is am looking:

If an employee has done production more than "0" than his attendance should reflect as "Present".

If an employee has done production less than or equal to "0", than his attendance should reflect as "Absent".

Can anyone please help me out to solve this.

I have attached here an attendance tracker that I maintain.

Now using Pivot Table I want to pull data in regards to information for each employee. I need to know the no of leaves he has taken in a particular month as well as on a total basis.
Also I need to know how many morning/afternoon shifts they worked in a month as well as on a total basis.

Please help if possible.

Hi all, I created a workbook that uses VBA userforms to add and retrieve and edit records from a worksheet. This works great when it isn't shared, but causes too many save conflicts when 5 or 6 people are using it at the same time. I have seen in other posts some references to using excel as a front end to an access database even if you do not have access installed. Can anyone point me in the right direction with this, any tutorials, links articles etc will be very welcome... thanks

Regards

Jay

Since I don't have access to Access I'd like to try and use Excel as a very basic database tool.

I have two different sets of data:

(1) A list of journals
(2) A list of seminars and conferences

Every time I use a particular set of journals to market a particular seminar or conference, I want to be able to link the two and be able to search AND sort on these later on.

So for example, I have Journal A, Journal B and Journal C.

I have seminar x and no that I used journal A and journal C for placing an ad. So I add this to the Excel database.

Later, someone wants to find out which journals we used for marketing seminar x, so goes off and does a search. Search comes back and says journal A and C.

Of course as I go on other seminars are also going to be using journals A and C, so another relationship needs to be created and so on.

I'd also like to be able to search on more than one seminar at the same time and produce one list of journals at the same time.

Finally, I'd also like to be able to perform the above in reverse, so look for a journal(s) and find what events were marketed with them.

Sorry about the longwinded explanation.

Any help much appreciated as always.

P.S. I AM A NEWBIE TO EXCEL

Ta

Hi all,

Is it possible to use the HYPERLINK command that open up another Excel as READONLY?

Since I don't want the user to close that Excel with the 'Do you want to save....' everytime.

Thx.

I am working off a document created by someone else in Visio. They have
create a 'spreadsheet' using lines and individual text boxes. I want to
import/copy this text from visio(ideally keeping the formatting, but not
essential) and paste it into Excel cells.

If I select one text box from visio and copy and paste it into excel, it
pastes the text into a cell as text (which is what I want). However, If I
select multiple text boxes it will paste this into excel as an image. I have
tried paste special but this only gives me different image types to paste it
as.

Can anyone assist or point me in the right direction.

Or am I barking up a non-existent tree?

Thanks!

Our office is using excel as a publications job tracking tool (I know, WHY?!...but I'm just the messenger! ). I have a workbook that has two active worksheets - the annual calendar on a week-to-week basis and an annual calendar on a day-to-day basis. The primary worksheet is the week-to-week calendar. The dates are in a header row in the day/month format.
Each job is listed in a vertical column with four rows associated with each (representing the person responsible for a specific portion of the process).

Here's what I would like to do:

When the week-to-week information is updated, I would like to have the day-to-day range for that particular week be automatically updated. The information that needs to be mirrored is a text block (person's first name) and cell color.

Does anyone know what formula, or code snippet I should use for this?

Hi all,

I am trying to save an excel sheet as a web page. But I am facing with the following problem:

The excel sheet when originally prepared,was compressed in size from 100% to 55% and images were pasted on it. Now when I save this excel as a webpage, it saves as 100% making it really big causing a horizontal scroll bar in the web page. This makes it very difficult to read and understand.

Is there a way to maintain the same size percentage and show it as it is on the web page also. That is save the excel sheet as a webpage keeping it at 55% ?

Your help will be highly appreciated.
Thanks,
Sreedhar

I have links to various spreadsheets on our intranet and would like to open excel as an application rather than within the IE browser so the full functionality of Excel can be used.

Reason: I have code within the open workbook routine 'Private Sub Workbook_Open()' that trys to show a form using .Show
eg) aform.Show

When it gets to this bit of code there is a Run-time error 1004. Close method of Workbook class failed.

Is it possible to use excel as a platform to chat with someone through internet (like MSN)

I have a report exported from SAP, saved as an HTML file. I parsed the file by Data - Text to columns, using "Fixed Width".

Each column now has blank spaces where the data string ends. For example, I have a string of numbers in a cell 9-9999-99 that now has 16 additional spaces at the end. (9-9999-99bbbbbbbbbbbbbbbb where b = blank space) I can backspace the blanks out, but it's not practical for 1000 lines in a report.

When I try to Edit - Replace the space with nothing, I get a message that Excel cannot find the space. I have look to see that nothing is checked in Match Case and Match entire cell contents, have tried Search by Rows and by Columns, and tried Look in Formulas and Values.

A work around I found was to paste this column of data into a Word document as unformatted text, the paste it back into Excel as text. I can then do Edit - Replace to remove the space.

I have many of these reports to format, so if someone knows of a way to remove the spaces without moving the data to Word first, it would be extremely helpful.

Thanks!

I have created and have been maintaining an Excel based configuration tool for almost 2 years now. Am self-taught with Excel as it helps with my IT sales job quite a bit. Response from my customers is overwhelmingly positive. Its been nothing short of astonishing. Response from anyone in management on the opther hand, is either indifferent or fearful/ignorant. They seem to see only risk, not opportunity. Will not let me take my ideas any further in their org.

I think I've found a large void that needs to be filled in my industry but unsure of what to do next. Can't seem to find much in the way of existing career paths for Excel based product config tools.

Are there any? I have no formal programming background of any kind, just a lot of 'learn as I go'. But I think that's the beauty of it. Did not seem too difficult to go from zero knowledge to where I am now.

Would appreciate any ionfo, advice, etc....

Just found this site tonight and its great!

AS

I am attempting to use Excel as a gradebook and I would like to total the
values of a series of columns where the values are not blank and are greater
than 0.
Should I test each value in a range for blank or >0?
If so, how would I go about doing that?
I have basic programming skills based out of Java (very elementary), and
would like to understand what I am doing. So if you reply, could you please
explain?

How do I start / run Excel as as service. Want to run Excel in the
background as a service to run automatic reports with out poping to the front
of the screen.

I need Help! The only way I can think to do this is create a separate
worksheet for each employee which we will interact with on a daily basis.
I'm struggling because I want to have a worksheet that collects all of the
data from each employees worksheet and puts it all together in one view. I
would love some suggestions on how to do it or on how I should design this
Attendance Tracker. FYI there are about 550 employees.

I use Excel as my gradebook using separate worksheets for subcategory. In the
test worksheet, I would like to be able to, for each student, drop the lowest
test grade. This may be a different test for each student.

Is Outlook Express can send a range in Excel as the body like Outlook?

I would like to write a marco to send a range in Excel with Outlook Express use Copy and Paste.

I have find some code from the Internet as the following:

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
Dim katie As Variant

Email =

Is it possible to use the contents of a cell in Excel as the criteria of a
field in an Access Query? I want to use Excel as an interface and reporting
tool for a data table stored in Access.

For example, in this theoretical interface, I would like to:

On a "parameters" Excel sheet, type a client name in a specific cell

On a "data" Excel sheet, refresh an external data range that is an Access
query that calculates typical metrics for only that specific client name

On a "report" Excel sheet, format the returned data in a presentable way

The major problem that I am having is that I do not know what needs to be
typed in the criteria of a Access query field so that it filters on the text
of a specific Excel cell.

Any help would be greatly appreciated,

I have a question about using excel as a simple database and publishing it to the web to allow other to interact with it. Please bare with me as I try to explain this:

I need to create a database of IDs for tracking purposes. Here is an example, UNICOL1001. The unique ID is composed of University Names (UNI), Colleges within the University (COL), department within the college (100), and class number (1).

So on excel I would create a worksheet with all the universities and their three-letter unique id. Then I would create another worksheet with all the colleges and their three-letter unique id and so on. Finally, I will have a "master" worksheet that will pull data from the other worksheet to create the ID (UNICOL1001).

So far so good right. My question is would it be possible to publish this to the web or intranet, so that other people in my group can get IDs or add new IDs to the the "master" list by adding records to the individual worksheets???

The way people would get IDs is through a GUI with a form asking them questions like from a drop down menu:
- What is your univeristy
- Which college are you in
- Etc.

Depending on their answers, they will get an output of a ID and if that ID is not already on the "master" worksheet, it would be added to it too.

Thanks in advance,
truongn2

I am attempting to use Excel as a gradebook and I would like to total the
values of a series of columns where the values are not blank and are greater
than 0.
Should I test each value in a range for blank or >0?
If so, how would I go about doing that?
I have basic programming skills based out of Java (very elementary), and
would like to understand what I am doing. So if you reply, could you please
explain?

I am using excel as a data base. It is very large What I am trying to do is
from word I want to look for specific data lets say like AD-02.56 then find
all the matches in the database and return the row that contain the serch
data i am lookingf for. it could be up to 20 rows that may have that data.
Is there a book that I can look for that can help me out in writing the macro
or what ever i will need to pull the information from excel into the word
document? I have 8 colums of information and like 500 recorde (rows) and so
on. I am just looking for a good book to get started on how to use excel as
a database.

Thank you for you help.

J.W.