I have a rowfilter on the location (so i can view who took the test by location nad whether or not they passed) - the only problem is that when it selects by location - it chooses the correct name, personellnum, location row but it then picks the test values ABOVE that row - not below it - so It is choosing the complete wrong values to go with the associated person -
My problem is that i am copying values from one sheet like: 1355,588846 and 456,23589 and storing them in a array. When i want to display the array in another sheet the values come out like 1355588846 and 45623589. So in the first sheet excel recorgnises the comma as a decimal number and in the other sheet as a thousand separator. How can i change this?
Develop one system that can search and make changes on the user form. I already find the solution on my first worksheet(USER PROFILE) but when i used the same coding on my second worksheet(INVENTORY&RET), it doesnt work. Here is the example of my INVENTORY&RET worksheet example.xlsm
I have a situation with a spreadsheet i'm working on.
Basically, when a button is pressed I want it to search for all rows which have a "yes" in an offload column (Column AS, on the sheet "Active") (So it can be yes or no).
If it finds a yes, I need it to move the whole row in to a new sheet called "inactive", then move the remaining results up.
I have taken and played about with a different VBA code, but i'm not sure it's even the right line!
Here is what I have :
Sub Refresh() ByVal Target As Range) If Target.Column = 45 Then If UCase(Target.Value) = "Yes" Then Target.EntireRow.Copy Destination:=Sheets("Inactive"). _ Range("A" & Rows.Count).End(xlUp).Offset(1) Target.EntireRow.Delete End If End If End Sub
I'm creating a Speech and Debate calculater that presents winners names by place according to their score. If you look at Tab HSSE Results it will read the inputed information from HS Speech English. I want the names of the students to appear in the correct ranking order in the respected grade rankings chart. I currently have a countif formula which reads the grade, but I think I need to link that with an index formula which I'm having problems with.
A workbook containing 2 sheets. Sheet1 is a data entry sheet containing 8 columns and many 10s of rows of client data. The columns are (in this order) Client, Date, Code, Duration, CaseNo, Description, Staff. I also have a second sheet. Sheet2 takes the data from Sheet1 and displays the results of certain calculations, such as amt of cases a particular Staff person has and of what Code type.
Example: - Total amt of P02 cases = 25 - # of cases Homer Simpson has = 10
So here's what I'm trying to accomplish: Is it possible to have Excel return the total amt of time spent on a particular Code type? It would have to search through the Code column for a particular Code type (let's say "P03") and return the SUM of the Duration which resides in a different column.
I have six colums (Date; Skill; ST Hrs; OT hrs: Area; Type). I copied and pasted below a small portion of info from the spreadsheet. I know when I copy and paste into a post the information under the headers don't line up exactly...it appears offset, so basically "Date" is of course the date you see..."Skill"'s columns has the first item as nb-mech b..."ST Hrs" first item will be 0.00..."OT Hrs" will be 1.50...."Area"'s first item is "Log FM NO"....and "Type"'s first item is MX07
And there are 17,000 rows of this info and forever growing...a lot of repeated items in each column such as you can see "blaster" has multiple entries on 4/1/2012.
What I am attempting to do which I have not firgured out yet (i have tried array formulas but they bog down the pc forver and couldn't figure out exactly how to make it give me the results I needed...and I also tried sumproducts but that didnt get me what I wanted....and it is being requested of me to NOT make a pivot table but a chart.
What I am trying to create is is the following:
1 - cells to plug in a date range for example cell A1 I plug 4/1/2012 and cell A2 plug 4/30/2012 2 - drop down boxes for columns Skill; Area: Type, which show only unique records in each column
Once I have that, I could then filter all 3 itmes and have only items show in the date ranges I picked and present ONLY the SUMS of columns ST Hrs and OT hrs.
Once the SUMS show up, I can then have cells off to the side that will give a percent break down of what percent of the total hours (for the chose criteria) are ST Hrs and what percent of the total are OT Hrs....and this percentage break down will show up in a Bar Chart, per day chosen in my date ranges....I'm thinking one simple stacked bar per day in the chart.
So say, based on the info below, I wanted to see the total ST Hrs and OT Hrs from 4-1-12 to 4-2-12 for a nb-blaster in LOG FM NO and type MX06.....my result would be 0.00 ST Hrs...23 OT Hrs.....and my bar chart would be one simple bar peaked out at 23 on day 4-1-12.....and within the bar it would be 0.00% ST Hrs and 100% OT Hrs and maybe the total hours for each showing also..don't need that but it may be useful.. ..and no bar for 4-2-12 (because the criteria picked had no hours for 4-2-12)
I have been messign witht is all week and just can't nail it.. I do remember a while ago I would have been able to load the spreadsheet into the post and people could see it in excel...is that option to do that no longer on this site?
I have to make a table that shows that a if someone purchases
less than 5 items they receive no discount 5-10 items they receive 2% discount 11-20 items they receive 5% discount 21-50 items they receive 8% discount over 50 items they receive 10%
and it has to be done in a way that the discount rate can be calculated using Vlookup I am struggling to find the best way to write this table. i tried numbering 1 to 50 and writing the corresponding discount rate in the second column but this looks untidy and can't calculate greater than 50 as i am not sure how to write it in the cell so it reads as >50 and not just 50.
I am using a For Each Next to cycle through a list of names, check certain conditions and then will be adding a further lookup (instead of Result = Result+1), but all I get is #Value!
Function maxbarometer(Name As String, Round As Integer, NameList As Variant, RoundRange As Range, RoundRangeTwo As Range) Dim Roundname As String Dim Result As Double Dim NameColRound As Integer Dim ListObject As Variant
Roundname = "Round " & Round NameColRound = Application.WorksheetFunction.Match(Name, RoundRange, 0) For Each ListObject In NameList If ListObject.Value = Name Then Result = Result ElseIf Application.WorksheetFunction.VLookup(ListObject.Value, RoundRange2, NameColRound, False) = Roundname Then Result = Result + 1 End If Next maxbarometer = Result End Function
when i try to get the final row witht the following:
KROGrow = Cells(65536, 1).End(xlUp).Row
i am getting a row that is consistently 27 rows off of when i manually do control up? i am pasting data from and existing workbook into a newly created workbook in the sub. i would assume that since the destination is new that they would never be any data in this?
I have the following formula: IF(SELL_INVESTMENT = "YES" , 1,2) It returns not 1 or 2 but 0. The worksheet contains over 1000 cells. Trace Precedents verifies that the formula evaluates the correct cell and that it contains "yes". I created a very simple worksheet to test the formula and here it correctly returns 1.
I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.
I have this workbook with 10 sheets. Each sheet has macros that are called from a floating toolbar. What I am trying to do:
I want to create a msgbox that warns me when I click in a toolbar's button to call a macro that not runs in the activesheet. Something like: "This code (or macro) doesn't match (or run) with the active sheet.
Thers no easy way to explain this other than looking at the sheet (which is attached), but basically i have a load time start time and end time. In the example in the sheet the load time is 01:00 The start time is 23:00 The end time is 23:35
Th eload took 35 mins and was not late as it is going 01:00 next morning, yet the answer it replies is 'N' which makes it show as late. Does anyone have a formula to slove this issue
The code is supposed to project review dates 1 month, 2 months and 3 months after the start date, but the first "cell offset" line on is projecting the next day instead of next month, its driving me nuts!!, the others are working fine
Code: Sub reviews() Dim cell As Range
For Each cell In Range("L1:L200") If IsDate(cell) Then cell.Offset(, 10).Value = DateAdd("m", 1, cell.Value) cell.Offset(, 11).Value = DateAdd("m", 2, cell.Value) cell.Offset(, 12).Value = DateAdd("m", 3, cell.Value) cell.Resize(, 10).AutoFill cell.Resize(, 11) End If Next cell End Sub
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.
And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.
So here is how it work out in excel: -6-(|-6/7|)*7 -6-(|-0.857142857|)*7 -6-(-1)*7 -6--7 -6+7=1
But in VBA you get -6-(|-6/7|)*7 -6-(|-0.857142857|)*7 -6-(0)*7 -6-0=-6
I have a very basic spreadsheet to calculate golfer handicaps based upon a course index. For some reason, my "LOOKUP" formula is not retrieving the data from the cell (column) next to the value I am searching...?