Formula: Absence Detail Shown
I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc
=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail))
My range names are correct and all have the same 'length'. F67 is 09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Absence Calculator
im trying to put together a system on worksheets that checks 'Absence' in a rolling 12 month period. The 12 month period is any 12 months and not a financial period (eg 25/12/06 -25/12/07). I have 36 employees and want to have their names in each sheet, calander dates across the top, will mark either a 'S' for sick or 'L' for late ect against the dates if not at work. Once an absence has been entered, on the sheet somewhere it will show how many days that person has been off (eg, 10th Oct, 16 Nov and 22 Dec would = 3 Days absence). How do I set up the sheets to work out how many days each person has had off in a rolling 12 month period (so that it does not calculate beyond the 12 months). I have looked on here to see if there are any programs, formulas which may work but some have lost me in my tracks.
View Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Formula Not Adding Correctly, Calculate The Results Based Off Of What Is Shown In The Cell
Basically a cell might have a number with 3 decimal places For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell. Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items. I included an example of what i mean in this excel file which should clear up any confusion.
View Replies!
View Related
Calculate Absence In A Period
how i would calculate the number of sickness days and occaisions in the previous 26 week period from todays date. I've attached the spreadsheet. So in C4 i want to have the total number of times(occasions) the person has been absent in the previous 26 weeks. and in E4 i want to have the Total number of Days (taken from E7:E20) the person has been sick in the past 26 weeks
View Replies!
View Related
SumIf/Sumproduct If Contains Detail
I have a list of names and corresponding costs charged for each entry. The names appear multiple times for multiple costs charged, but contain any combination of characters after their name. I am looking to get the sum of all entries for each name. I have tried the following formulae, but it does not return any information: =SUMIF('Jan line items'!E:E,(CONTAINS:A:A),'Jan line items'!F:F)
View Replies!
View Related
Track Changes Detail In A Cell
Is there any way to capture the comment that comes when we use "Track Changes", the date & time when the cell content changes should be retrieved in the adjacent automatically. Is there any way to do it using formulas/ VB code.
View Replies!
View Related
Pulling Detail To Second Sheet W/o Zeros
I have two sheets....one sheet has a all the detail I need on it. On the second sheet I am trying to pull all the detail by category but want to eliminate any zeros in between. Anyone know how to do this? I feel like I am being very vague so if I need to give more detail just let me know.
View Replies!
View Related
Lookup Related Detail Using 2 Columns
I want to write a formula so that if an item in column A = " name" and column B = " date" (name and date to be selected from a drop-down box), then it will return the adjacent cells text in column C. For example; ColAColBColC nick01-Marabc ltd mick01-Mardeliver it dan02-Mardiy nick03-Margo ltd mick04-Marfreebie dan03-Marhelp ltd If I select "nick" and "3/3/08", then the formula will return "go ltd". I know how to do filters, my aim is to create a master sheet and then for someone else to be able to select their name and a month. The month will then update a mocked up calendar (i.e. a 7 by 5 sheet where cell A1 = Date selected, A2 = Date+1, A3 = Date+2 etc). I've attached a basic mockup of what I want to do. If I select nick and march, the yellow highlighted cells should populate in C9 and E9 with abc ltd and go ltd, the others blank.
View Replies!
View Related
Pivot Table Detail Report Format
When you create a pivot table and double-click on the output of the pivot repot you access the detailed info that make up this particular number. Is it possible to preserve the format of the raw data file that has been used to create the pivot table output? For instance, the raw data has $. When I create the pivot table I can format the table to reflect the $. However, when I double click on the pivot table number where I have the $ I access the detail info but the format (in this case $) is lost. I called Microsoft and told me it is a flaw in Excel. But, I'd like to check with you before I give up. If I can't do that. I have an alternative: double click on all the numbers of the pivot table then format the sheets to include the $ and then create a hyperlink from the pivot table to the detailed report tab.
View Replies!
View Related
Show Detail Event In Pivot Table
When you double click on a number in a pivot table data area you get a new sheet with that information showing the detail, is there anyway to "trap" this action? I want to run a macro on the detail data but can't see a way to do it automatically, have tried, BeforeDoubleClick, PivotTableUpdate, NewSheet events but all have there draw backs if you are doing something other than showing the detail for the pivot table. Of course I can just run the macro after the sheet is made but it would be nice to do it on its own.
View Replies!
View Related
Show Pivot Table Detail Macro
I have recorded the below macro to select a pivot table field (on demand) and show the information on anthother sheet tab. Obviously if the order of the fields change then the macro is buggered, any ideas so it only selects the on demand field and shows that info. Sub Macro1() ....
View Replies!
View Related
Apostrophe: Fill Out A Table With Some Detail Information
I use the GetObjects function to gain access to the properties of a mailbox and want to fill out a table with some detail information. The code works fine until I have a mailbox which name has an apostrophe included. Set o = getobjects("winMgmts:!\VS1 ootMicrosoftExchangeV2:Exchange_Mailbox.LegacyDN='/O=TEST/OU=TEST/cn=Recipients/cn=co'brien',MailboxGUID='{59A40254-9F12-4502-9A03-3FB5CB0BBD38}',ServerName='VS1',StorageGroupName='sg1',StoreName='mb1'") I highlighted the section that causes the problem. The string within the string is encapsulated in apostrophes, hence the apostrophe in the mailbox name co'brien confuses the code and raises an error. Microsoft provides a solution by using escape characters, but that seems to work only in vbscript, not in vba. Here is a link to Microsoft's solution: http://www.microsoft.com/technet/scr...4/hey1217.mspx I tried some workarounds that are being used for a similiar problem with SQL statements as well, but again without success - these are the versions I tried: co/'brien co'brien co''brien
View Replies!
View Related
Lookup Across Columns & Return Matching Detail
I am trying to track a score. A master capacity sheet has two columns with identifiers (upstream and downstream nodes) relative to a pipe segment. I have a separate sheet (condition sheet) with the corresponding scores. I want to enter the upstream and downstream node numbers in 2 columns (A & B) in the master capacity sheet, and based on the the upstream and downstream node numbers, have the corresponding score (from the condition sheet) on that row copied in the master capacity sheet.
View Replies!
View Related
Summary Sheet Calculate Results Change If Rows Added/deleted In Detail Spreadsheet
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation. I'm using dynamic name ranges, as follows, for the detail: WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1) Other detail data is defined as these examples show: GM_X =OFFSET(WO_Num,0,8) OpenDate =OFFSET(WO_Num,0,1) All detail data begins in Row 6 in the detail spreadsheet. In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand. Here are two examples of the calcs I'm using in the summary: =COUNTIF(WO_Num,">0") =COUNTIF(GM_X,"X") Can someone tell me what is going on? What I'm doing wrong?
View Replies!
View Related
Value Shown As #####
I have a value showing up as #####. When I auotfill the cell to the next on the right, it shows the right value. How do I correct this so it just shows the right value in the first cell?
View Replies!
View Related
Macro Button That Is Always Shown
How do I programmatically freeze ( lock?) an autoshape so that it stays put even when the user scrolls up/or down? FYI: I created an autoshape and assigned it a macro, thus it becomes a software button, I want the button to stay put.
View Replies!
View Related
Percentages Shown At The Top Of Columns
Columns O,P and Q should show a "Y" if Column N has the percentage (the percentages shown at the top of those three columns) over the value in column D. I hope this makes sense! ******** ******************** ************************************************************************>Microsoft Excel - Team Lead Incentive.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF3H3N3F4H4N4= CDEFGHIJKLMNOPQ1***WF*Team*Lead*Incentive****As*of*4/5/07***(15%)(20%)(25%)2TeamBase*Team*****Hours******Wk*1Wk1*%Wk*2Wk2*%Wk*3Wk3*%Wk*4Wk4*%*Total$75*$100*$125*32600.25*592.00-1.37%623.753.92%*****2.54%***43388.00*467.5020.49%527.5035.95%*****56.44%***WF* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
First IF To Be Ignored If Specific Column Contains Any Of The Words Shown
=IF((P3<$Q$1),E3-S3,IF((G3="B"),"Not Changed",IF((G3="MT"),"Not Changed","Not Changed"))) Above is the formula I'm trying to get to work but it's not-a-happenin. It seems like the first IF overides all of the others. Basically I want the first IF to be ignored if column G contains any of the words shown. So basically what I think is happening is that P3 is less than Q1 so it's ignoring the rest of the IFs. Originally my formula worked fine as: =IF(P4<$Q$1,E4-S4,"Not Changed") But I realized that i needed a certain category of product ignored. Products are labeled in column G.
View Replies!
View Related
Code Stops When UserForm Shown
I have an Access program that acts as a dashboard to open Excel reports. One of the Excel workbooks opens a form in the Auto_Open routine. My problem is that control is never passed back to access after the form opens. It appears that the code stops executing until the userform is closed. How can I open the excel workbook, show the Excel userform, and continue processing my Access code? Set XL = Excel.Application With XL .Workbooks.Open FileName:=FileName, ReadOnly:=True . ActiveWorkbook.RunAutoMacros xlAutoOpen End With XL.Visible = True ' Excel Code Sub Auto_Open() Unload UserForm1 UserForm1.Show ' The code get "stuck" right here End Sub
View Replies!
View Related
When Receive More Than Order As In This Table The Pending Must Be Shown 0
i have the following kind of table Raw Material ReportDateDetailOrder Given Received Opening Balance25-Oct-07order to Johson50029-Oct-07Receive from jonson10014-Jan-08Receive from jonson 5405Pending sum500505-5 as you can see that the pending order (receivable from johson) is -5 as we reveive more then order given. i want here that when we receive more than order as in this table the pending must be shown 0. i can do it by apply the condition when pending is
View Replies!
View Related
Limit The Number Of Times Shown In A Diagram
I'm making a line diagram, showing how a value changes over time. My data is measured every 30 min, which results in a mess when it's plotted over a month. How do I limit the number of times shown on the x-axis, for instance so that only the date (every 24 hours) is shown?
View Replies!
View Related
IF Function (few Words Shown Based On Figures In Specific Cells)
I want to have a few words shown based on figures in specific cells. I not quite sure how to explain, but i will try my best: A1 = 5 A2 = 0 A3 = 7 The function i am using looks something like this:- =IF(A1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?") However, by using this function, if the cell is empty it still shows the first wording when i need it to show lets say between 1 & 6 shows the first wording and more than 6 shows the second wording! If you know what i mean... I know what i want to do but cant really explain it very well. Maybe it should look something like this but it does not work the way i am doing it. =IF(E156>1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
View Replies!
View Related
Row / Column Header Not Fully Shown In Normal View
Recently I come across a file with only 1 sheet, in which is a table only occupied an array of A1:Q49. In Normal View I can see the row / column header of A-Q and 1-49 as usual, but nothing outside that range (only grey colour). Also, the empty cells outside A1:Q49 are all grey too. How can I do that?
View Replies!
View Related
Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones
I am getting values for my excel sheet from another department excel sheet . everything works fine. If there is no values in the rows in the Department sheet, then i need to hide the rows in my sheet. How to code this in VBA. When they add values to the rows then i should make the rows visble here. Kindy give me a sample of vba code to this or suggest me to solve.
View Replies!
View Related
Rankings From Sheet 1 Shown In Sheet 2
Is there a formula-based way to do the following? Sheet 1 has last name, first name, sales, and region in columns A through D, respectively. Sheet 2 is a rankings sheet of sheet 1. Sheet 2 contains 2 headings: Top salespersons for region A, and top salespersons for region B. I'd like for the top 2 salespersons' (in each region) names & sales amounts to appear under their appropriate region in sheet 2. I just don't know which formula to start with that would be able to look at sheet 1, sort the top salespeople in each region, then display those in the right order on sheet 2.
View Replies!
View Related
Formula For Deleting A Formula When The Formula Has Done Its Calculation
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating. I've got a formula =IF(COUNTA(A1)=1,TODAY(),"") When something is typed in Cell A1 the cell with the formula will input todays date. Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.
View Replies!
View Related
Combine Formula Which Reduce The No. Of Additoanl Formula's Columns
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass. To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them. Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
View Replies!
View Related
Add Addition If Condition To Existing Formula: Long Formula
This task joins a string together based on a number of characters per cell in the range. I want to isolate one range, Col N, and add an IF condition to it. There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
View Replies!
View Related
Date Formula & Add If Statement To The Existing Formula
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
View Replies!
View Related
Replace Character Used In Formula Where Formula References Cell
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url. i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6 this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
View Replies!
View Related
Copy A Formula In A Cell And Then Paste Only The Text Of The Formula
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text. When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.
View Replies!
View Related
Formula's Keep Disappearing- Formula Does The Calculation And Then Disappears
I have a very large spreadsheet which holds a lot of data, and has a custom reports system built into it, (i.e. running on a load of macro's)... The reports gather their info from a range of hidden cells which run different formula's to provide such results as 1 or 0 so that it collates into another sheet... however, I have found recently that when I need to update any of the formula's and whatnot, the formula does the calculation and then disappears... whereas I need the formula to be there constantly so that the report is providing the correct information! one such formula is: =IF(AND(AC11"No Calc",AC11>=1,AC11
View Replies!
View Related
Use The Results Of A Formula As Column/row Numbers In Another Formula
I have two cells. The first cell has the formula: =CONCATENATE("D",TEXT(MATCH($B$6,'Zip Ranges'!$D$1:$D$157,0)+1,"0")) which results in a col and row number (such as D65). The second cell has the following formula: =INDEX('Zip Ranges'!$A:$B,MATCH($B$6,'Zip Ranges'!D1:$D$157,0),2) ^^ I wish to replace the 'D1" in the Match function with the results of the first cell's formula. I assume Indirect would work, but I don't know how to code the formula to use it.
View Replies!
View Related
|
TRACKER
