inputting a formula that will look at the dates in row 2 on the tab marked “Cap Plan” on the sample sheet and then go to tab “Monthly Mode” and match the date and then take the percentages for each work type on that tab and put them in the percentage columns in the tab “Cap Plan” for the correct date. This document will be a rolling 52 week one so a simple = formula will not work.
View 2 Repliesauto calculate end dates on production plan ...
View 9 Replies View RelatedI have a calendar in the sheet attached. If there are leave dates that are marked in red, can I create a formula to count the number of leave dates for the entire year ?
View 5 Replies View RelatedI have a spreadsheet that keeps track of my travel. Column A has the date I arrived somewhere, and Column B has the date I departed, and Column C has the name of the city I went to.
I am wondering if there is a way to generate a calendar using my list that will mark those dates. For example, a calendar for the month of June 2008 that would show I was traveling from June 3 to June 14, either by marking those dates with a different color or labeling them with the city names, or even just putting an x in the box.
I am using HLOOKUP to show a date depending on the month shown in B10.
If B10 is January then the dates throughout January ie. 01/01/08 to 31/01/08 are shown in the cell range of F13 to F43. There is no problem with this unless there are 30 or 29 days etc in a month. So if November is in B10, in F43 I have the value of 00/01/1900. Can I modify my formula so if there is no data in the lookup table to return a blank value ie. "".
Here is my formula:
=HLOOKUP($B$10,DATES!$B$1:$M$32,2,FALSE)
I obviously change the 2 to 3, 3 to 4 as I copy the formula down the table.
i'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:
If student missed exam and has a zero – keep zero.
If student has a grade of 1-119 points, increase their grade 40 points.
If student has a grade of 120-125 points, increase their grade 35 points.
If student has a grade of 126-131 points, increase their grade 31 points.
If student has a grade of 132-139 points, increase their grade 27 points.
with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error.
=H4+HLOOKUP(H4,B24:D25,2).
I am trying to import a BASE ESTIMATE table into EXCEL.
I have problems with most of the formulas, especially this one:
=VLOOKUP($E$2,$B$24:$P$604,HLOOKUP($E$3,$D$22:$L$604,1)+2)*HLOOKUP(HLOOKUP($E$3,$D$22:$L$604,1),$D$2 2:$L$23,2)
and this one
=ROUND((IF(AND(OR(E7>E5,E7>E6),E3<40000),E7,IF(AND(E6<E5,E3<40000),E6,E5)))*E8*1.055*1.06,2)+10
I am not sure if EXCEL allows a HLOOKUP within an HLOOKUP. If not, how can I get around this?
how to use formula in the monthly contribution field to calculate 7% of pay (salary)
Lenght of Total pay Monthly contribution cummulative
Service retirement value
3.6 $528.94
Please one more thing. How do i use FV Function, monthly contribution, and the lenght of service field to to determine the current value of the employees retirement palan.
I've attached a simplified worksheet which tracks the calls of a team of salespeople along with a pivot table showing their results for Jan-Mar. I've also included a summary of their planned calls for the period. Ideally what I'd like to do is have the pivot table show actual calls, planned calls and variance for each salesperson and each month as seen on A37-J42.
View 6 Replies View RelatedI work on an Excel file to determine property date of entry. While I have not completely worked out all of the bugs or the permutations or problems that may come up, I do feel that I have discerned an important part of the problem. The sheet where I set up my formulas and functions has the name 410a elig and coverage and should have a green tab.
To explain my reasoning; in this hypothetical situation, a company's plan has two dates of entry, January 1st and July 1st. An employee enters the plan on which of these two days follows closest or coincides with his or her attainment of 1,000 hours of service during the twelve months forming his or her initial eligibly computation period. If an employee starts work from January 2, 2005 to July 1, 2005, then that employee would enter the plan (provided he or she worked 1,000 hours during the initial eligibility computation period) on July 1st, 2006. If that employee started work from July 2, 2005 to December 31st 2005, then that person would enter the plan on January 1st, 2007.............
Some of the abbreviations or acronyms spelled out:
OYFDOH=One Year From Date of Hire; for now, the issue of leap years I will set aside
Month Function
Month=Month Function
JJ: January or July
OYFDOH MON JJ Year
8/7/2006 8 1 2007
1/2/2006 1 7 2006
6/1/2006 6 7 2006
3/1/2006 3 7 2006
8/1/2005 8 1 2006
6/1/2006 6 7 2006
Instead of having the goals and objectives already in the form I would like to have buttons that would allow my clinicians to add as many goals and objectives as they'd like to the attached treatment plan. I'd like them to be able to click a delete button as well when they review the plan and need to delete a goal that's been met. When my team clicks into the second worksheet they would see rows 10-16 (below). They would then type a STG 1 and an Obj A. They then would click the Add Obj button and add as many objectives that they needed. Then when they clicked the add goal button a blank copy of rows 10-16 (including the Add Obj button) would pasted one row down and would update to read STG 2. Neither of these buttons would appear when the document printed.
Here is a screen scrape of what I'd like the attached form to look like before goals and objectives are added.
add goal.jpg
The original form is saved in an .xltm format but I saved it in an .xlsm format so I could upload it here.
HCT ITP (2-25-14) Abbr.xlsm
I found a thread on this forum that came close to answering my question but my attempts to pull out the coding that I needed was unsuccessful. Here's a link to the thread: [URL] ........
I think the title pretty much says it all... Now I have a file I can't send to anyone to do anything with... I've googled a few things but I've found nothing to work with... It was only a few hours worth of work but this seems ridiculous...
View 3 Replies View RelatedIf possible I want to know if the code below can be changed. At the moment it searches for cells not marked with an X and then prints the remaining cells.
View 8 Replies View RelatedIn Excel 2003, I have a checkbox in a cell (C15). I want a range of cells (F15:O15) to respond to this box. When the box is not checked and someone attempts to alter one of the cells in the range, I want a message box to appear, and then to have the data input in that cell removed.
The cells in the range are a mix of drop down lists using data validation and check boxes, so I need to make sure it doesn't erase those, just the choice made from the list or the mark in the box.
Possible?
Below is a part of my code. But I am getting error on the bold&Red marked line.
View 2 Replies View Relatedi'm having a bit of a issue with a do while loop. It might just be my complete lack of understanding, but i'll briefly explain what I am doing before I show you the code. I am marking cells with what I consider an invalid type for that cell red. Well unfortunatley in some sheets the number of invalid types is so high the macro crashes. So my bright idea was to inclose the check statment into a do while loop, that will only check until so many cells have been marked red. So I created a public variable, assigned it a value of 1. I then assigned the macro that marks the cell red to also take y and add 1. So in theory (mind you what I take for theroy might be in my head) it should only run until the paramaters of the do while are met which in this case are do while y < 20.
y = 1
Do While y < 20
Set MyRange = Intersect(Columns(7), ActiveSheet.UsedRange)
For Each r In MyRange
CheckDate
Next r
Loop
That is the snippet from the program calling the function, and here is the function
Public Function CheckDate()
If IsDate(r.Cells) = False And IsEmpty(r.Cells) = False Then
r.Select
MarkDate
y = y + 1
End If
If r.Cells < 1 / 1 / 1910 And IsEmpty(r.Cells) = False Then
r.Select
MarkDate
y = y + 1
End If
End Function
what i'm doing wrong here? Oh and y is assinged as a public varaible, integer data type.
I have this excel workbook that when i tick the first sheet ("251" in temp), it copys the row onto the second sheet ("order" in temp). This all work wonderful, but now if i would like to add additional pages to this excel workbook and have it do the same thing (by same thing i mean adding additional sheets but keep only one "order" worksheet and have all the information go onto the order worksheet.
So for example:
I would add an additional page name 252 into the workbook, it would look and function just like the 251. So after all the ticking on 251, all the information would go onto the "order", i can then move onto 252 and do the same type of ticking of information and those information would also continue onto "order". And i can keep adding multiple worksheet onto the workbook and do the same exact thing.
I run a football sweepstake involving 120 people. Each week everyone picks a premiership team who they think is going to win. If that team loses or draws they are out. This carries on until there is one person left and they win the money. I need to make a table with all the premiership teams and names down the x and y axis. When the team is marked as "lose" everyone who has chosen that team gets deleted.
View 5 Replies View RelatedI have a very large spreadsheet with the following columns: WO# (number field); Start date (date format MM/DD/YYYY); Frequency (text); and craft (number). I am trying to have code that checks the frequency and if is "Monthly" or "Weekly" it just goes on to the next row; if it is "Annual", it adds 163 to the start date (start date needs to changed to a numeric field); if it is "Semi-annual", it adds 82 to the start date; if it is "Quarterly", it adds 45 to the start date; and so on, there about 20m different frequencies. After it adds the above value to the start date, I need to check if that number is less than today's date (the day I run the code). If it is, it needs to flagged as "LATE" and the whole row of info copied to another worksheet with LATE as the title and all the column headings and info copied to the worksheet. I hope this makes sense to someone because I am a beginner in Excel and even less informed when it comes to VBA. Any help would be greatly appreciated.
The way the process must work is that I need to check the frequency and if it is "Weekly" or "Monthly" , it is ignored and goes on to the next row. All other frequencies are cut in half, i.e., "Annual" is 183 days, "Semi-annual is 92 days", "Quarterly" is 45 days, "2-Year" is 365 days, and so on. This number needs to added to the scheduled start date (now formatted as a number, not a date, and checked to see if it is smaller than today's date (also a number). If it is, it is reported on the second worksheet (titled Late).
I am trying to build a user form where people are filling in their holiday requests.
I need once the date is choosen to be marked somehow on a calendar in the same workbook.
Besides the date, I need in this form, people to upload a file "Holiday Request" which, after the submission of the report to be sent to a specific email, and to be stored as Object in another sheet against the name of the person who is submitting it.
I am using a For Next statement that doesn't return the results for all the rows. The statement is as follows:
For Row = 1 To 100
If ActiveCell.Value = "CHANGE" Then
ActiveCell. Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(""0000""&RC[-1],20)"
ActiveCell.Offset(1, 0).Range("A1").Select
ElseIf ActiveCell.Value <> "CHANGE" Then
ActiveCell.Offset(1, 0).Range("A1").Select
Else: Range("A1").Select
Exit For
End If
Next
Range("A1").Select
I hope I did that according to the rules. It only returns the result in the first cell that does have a value of "CHANGE". It seem to be going through the entire range of cells, but I'm not getting any results.
Formula to automatically do these operations in the table below?
"column I1" contain data which I need to find in between columm B1:H1; and marked them red.
I need to do same operation for row2 to row4. I need a formula that can automatically find and mark the data in red.
I need to put a formula in B6 to count the data marked in red for column B1:B4 and do the same operation for C6,D6...H6.
A
B
C
D
E
F
G
H
RESULTS
p1
11
01
12
22
21
M1
10
11
[Code] ..........
Automatically copy rows to new sheet in excel when column marked with an X. If a column is marked with an X, I need to copy this row to a new sheet. So if a column C is marked with X, I need to copy this row in Sheet2 , if a column D is marked with and X, I need to copy this row below next empty row in Sheet2, if a column E is marked with X, I need to copy this row in next empty row in Sheet2.
View 2 Replies View RelatedI have spread sheet and values in some columns have highlighted in color.What I have to do is I need to quickly separate the highlighted data and put in the other spreadsheet.
View 5 Replies View RelatedIf a column is marked with an X I need to copy this row to a new sheet. So if a column C is marked with and X I need to copy this row to Sheet2 , if a column D is marked with and X I need to copy this row to Sheet3 and if a column E is marked with and X I need to copy this row to Sheet4., Please can someone help with the VBA code to make this work?
View 10 Replies View RelatedAttached workbook has two spreadsheets
1. Floor plan with the seats
2. Status of the seats with their types.
I want conditional formatting for each of the seats based on its type mentioned in the Master sheet. For ex - Seat 1 is categorized as "A", so cell AE4 will have a color, similarly all the type A seats will have same colors. Next all type B seats will have a different color. IF a seat is vacant, it should be shown as Green.
I am using an XY scatter chart that displays the planned path and an actual path as I go. The planned path is set up in 100' increments and goes 12000' and actual path varies along the way.
My data that I use is the Northing (ft), Easting (ft) and Vertical Section (ft) for both the planned path and actual path.
I was wondering if there was an east way to get that information from the chart or if there is a formula that would allow me to calculate distance between the two points by using the data I use for my chart.
I am trying to determine that if a range of cells are marked with an "X", then cells containing "Pass" (A4), Pass with Opportunity for Improvement (A6)or "Fail" (A8) will be marked with an "X". I have 3 columns that are selectable for Pass, Fail, and Non-Applicable. There are a total of thirteen questions that this evaluation form asks, and the evaluator must check one box. In order to be considered a Pass, there must be more 12 or more "X"'s marked in the Pass column. A Pass with Opportunity for Improvement would be equal to 11 marked in the Pass column, and a Failure would be 10 or less showing up in the Fail column. If there is a check in the non-applicable section, this has to be figured into thye pass fail as well. A Pass would be 85% or higher, and failure anything less than this. I will attach the file for review. I would very much appreciate help on this problem first, and then there is one other element to the sheet that plays in at the end.
View 13 Replies View RelatedIn L45 I need function
=IF(L41>0,IF(L44>0,IF(K44>0,(L44-K44)-L42,IF(J44>0,(L44-J44)-L42,IF(I44>0,(L44-I44)-L42,IF(H44>0,(L44-H44)-L42,IF(G44>0,(L44-G44)-L42,IF(F44>0,(L44-F44)-L42,IF(E44>0,(l44-E44)-l42,L44-L42))))))),L44-L42),0)
Fragment marked in red is currently missing and if I want to use it, I would be nesting too many if functions again. To describe the situation: Row 41 is staff available for task, sometimes there is nobody doing the task therefore function will force value 0 in all variances if there is no staff available.
There are also occasions that despite having a number of staff allocated to do the task, nothing’s been received due to a various reasons, this is where I have designed a function calculating hourly variance against what was received against the planned target for this hour. When there is no staff – everything will be 0
When nothing’s been received – target for the hour will be negative. When there was something received – the difference between last receiving figure and current one, minus target for an hour will provide the hourly rate, but If nothing’s been received for the last couple hours, it will go to the last hour when something was received and calculate hourly rate basing on the difference between current figure, last available figure and target for an hour. And here we are at the heart of the problem – in the last cell I cannot use same function as I would have to nest too many “if” functions, therefore it does not include receiving figure in E44 at the moment.
I am facing a problem using Hlookup function. I am using Hlookup to do a control check of consolidation i am doing here, however it only returns the first found value instead of all correct values (or range).
I am attaching a file with an example. In the file formula is used in Supply(S) sheet on row 73.
Attached File : HlookupExample.xlsx‎