Retrieving Variable Rows From Another Worksheet: Reducing "Calculation Time"
I'm using this formula to pick all rows from a huge range of cells "DATA" in another sheet, by matching the first column value (SAPDATA is the range that contains all the first column cells of DATA).
{=IF(ROW($B49)-ROW($B$48)>COUNTIF(SAPDATA,$D$4),"",INDEX(DATA,SMALL(IF(SAPDATA=$D$4,ROW(SAPDATA)),ROW($B49)-ROW($B$48)),2))}
All possible first column values have been stored as a dropdown in cell D4, and whenever I change the choice in the dropdown cell, the retrieval of those hundreds of rows takes ages in excel.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Variable Time Calculation
[code] ...... =SUMIF($A:$A,">="&TODAY()-1,D:D)-SUMIF($A:$A,">"&TODAY(),D:D) calculates the total in Col D looking back from today. The results gives you 12:00. What I need is a formula that will look back from the current time to 24 hours in the past, NOT 1 day, to calculate the total in Col D. Looking back 24 hours, from 10:00am, the results would be 6:00. Since the first time on 12/12 has not arrive, it will not calculate it, but will look back from 10:00am on 12/12 to 10:00am on 12/11 and calculate a result of 6:00 hours.
View Replies!
View Related
Reducing Resolution Of Worksheet Data
I have a piece data acquisition hardware which stores data every 5 minutes into an excel sheet, I need to reduce the resolution of the data to every 30 minutes, currently I am doing this by deleting five rows leaving one and repeating the process, there is however 9285 rows I am assuming there is a macro which can do this far quicker than me.
View Replies!
View Related
Reducing Multiple Rows - Duplicates In Column A
I can't figure out a solution. 4 columns, thousands of rows. Column A has alphanumeric data that can appear, once, twice, 3 times or 4, but never more than 4. I want to see this spreadsheet so that each piece of data in column A is presented only once. The information in the other 3 columns isn't important for what I want to do, so I need to reduce 20000 + rows, which includes many duplicates in Column A to about 5-60000, but need help figuring the correct formula/macro ....
View Replies!
View Related
Run-Time Error Setting Worksheet Variable
The user is asked for one piece of information "Enter the ID Number. What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works. Set Wks2 = Worksheets(strWks) 7 rows from the bottom. My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks. Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number. I get a Time Run Error 9. It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks. Sub Macro01C_Auto_Resign() Dim Wks1 As Worksheet, Wks2 As Worksheet Dim strFind As String, rngFound As Range Dim lngRow As Long, rngUnion As Range, strWks As String ............................
View Replies!
View Related
Adding A Variable Rows Worksheet
Due DateInvDocument numberDebitCreditBalance 17/09/08L01607610714211201825.44 13/10/08L01637010284345841020 13/10/08L01637131713874501020 13/10/08L016373852202473461020 13/10/08L0163748.5222E+111020 15/10/08L01641811103746832316 The above is a part of a w/sheet. Rather than determining the last row in the debit column and credit column (to know which is greater) and use a loop to put a formula in each of the cells in the balance column (adding the debit and credit), I know I can use the with ... statement to input the formula at a go , but cannot figure it out. Sub sumtotal() With Sheets(1) With .Range("G14", Range("G" & Rows.Count).End(xlUp)) .Clear .Formula = "=sum(E14+F14)" On Error Resume Next End With End With End Sub
View Replies!
View Related
Run Time Error 91 Object Variable Or With Block Variable Not Set
I am having a lot of trouble finding out why I am getting error. I believe the error is because it can't find the number. In cells C115:C314 i have the numbers 1 to 200 in order. when someone types in 1 to 9 in the text box it works, but on 10 and over i get the error ???? here is the code I have shCalculator.Range("C115:C314").Select Selection.Find(what:=CInt(txtPackageID), After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Select x = ActiveCell.Row shCalculator.Range("ProposedMeter").Value = Cells(x, 7).Value shCalculator.Range("Package").Value = Cells(x, 12).Value shCalculator.Range("ProposedMeterAmount").Value = Cells(x, 30).Value shCalculator.Range("Term").Value = Cells(x, 62).Value shCalculator.Range("Discount").Value = Cells(x, 67).Value shCalculator.Range("Equipment").Value = Cells(x, 72).Value
View Replies!
View Related
Run Time Error 91, Object Variable Or With Block Variable Not Set
Function Find_Range(Find_Item As Variant, _ Search_Range As Range, _ Optional LookIn As Variant, _ Optional LookAt As Variant, _ Optional MatchCase As Boolean) As Range Dim c As Range If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole If IsMissing(MatchCase) Then MatchCase = False With Search_Range Set c = .Find( _ What:=Find_Item, _ LookIn:=LookIn, _ LookAt:=LookAt, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=MatchCase, _ ........................ Using the message box I see that sheet 1 opens but then I receive an error message Run Time Error 91, Object Variable or With Block Variable not set. I tried declaring and using set on "project" but got nowhere. I also need to have a message indicating project not found. Once this part is solved I will loop all of my other workbooks
View Replies!
View Related
2007 Macro: Run-time Error 91:Object Variable Or With Block Variable Not Set
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro. But it ends in a Run-time error 91... The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile. Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile. The next target file in the folder is opened and the actions are repeated in this second target file. For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook. The error message i get is: "Run-time error 91:Object variable or with block variable not set." When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
View Replies!
View Related
Calculation Based On Variable
Im trying to access data from 2 different cells (say A1, B1), its for calculating transformer hotspots. The cooling method of the transformer is naturaly air cooled oil.. until the winding temp reaches 75C ( I have winding temp data laid out on a column) then the fans come on and some constants in the formula change (need to read from the other cell, say B1) so it switches, but the fans stay on UNTIL the winding temp is 50C and then go off, so the characteristics change back to the previous one, so I need to switch back. How do I go about doing this... my current method starts working when it reaches 50 instead of waiting the temp to go to 75 then fall to 50 then do it... im a bit confused.. maybe i shouldnt be doing this on excel... ow well
View Replies!
View Related
Calculation Of Time With Two Different Time Formats
Need a point in the right direction with this one! I want to calculate the difference between two time values, the problem is there two different formats as below:- 1) 520.00 - this is a number format 2) 500:00:00 - this is a time format HHH MM SS What could I use to show I have a 20.00 hour difference???
View Replies!
View Related
Calculation Error After Passing Values To Variable
I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4. But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway. If ComboBox2.Text = "Pounds (£)" Then Sheets("Claims").Range("G16") = TextBox2.Text Else Sheets("Claims").Range("F16") = TextBox2.Text End If Sheets("Claims").Range("B16") = ComboBox1.Text Sheets("Claims").Range("C16") = TextBox1.Text Sheets("Claims").Range("B16") = TextBox3.Text Sheets("Claims").Range("D16") = TextBox4.Text Sheets("Claims").Range("E16") = TextBox5.Text Dim a As Long Dim b As Long Dim answer As Long a = Sheets("Claims").[E16] b = Sheets("Claims").[F16] answer = (a * b) Sheets("Claims").[G16] = answer
View Replies!
View Related
Calculation On Time
I have been burning brain cells trying to figure this out. I get these numbers from an online source and they come in like this: A B C D E 1/1/0912:01AM02:40AM11:18AM07:55PM The times do not come in as times...when I format the cell to time it doesnt change...that is my first problem. What I would need to do to these times is: take B and C and find what time is in the middle of them and put that in a different column. This mess will also need to be plotted on a chart with time by the minute for one day as the X axis. In my example I drew lines on the chart to show what I mean....the blue lines I dont want charted...I use those to find the time in the middle.
View Replies!
View Related
Time Calculation ...
Having been looking round this site for quite some time now and always finding what I needed I am now a registered member who needs your expertise. I have a spreadsheet for which I need to calculate hours worked depending on a few criteria. [data] ... The criteria is that Sat/Eve is 8pm to 6am weekdays and midnight to midnight on a saturday. Sun is midnight to midnight on a sunday, BH is a bank holiday and basic is everthing else. What I want to know is it these columns can be populated automatically using formulas. I would really appreciate it if someone out there is up to completing this challange, as I have to manually populate this at the moment and it can be 5000+ lines long (it takes hours). If i need to change the layout it's not a problem, whatever it takes to automate it has got to be worth the effort.
View Replies!
View Related
Same Time Calculation
I am trying to get a total column that will give the total only when two particular devices are down at the same time. This total will be taken from a long list of downtime entries for different devices but I only want the total when two particular devices are down, for example Devicedatedowntimedateuptimetotal time 102/01/0911:00:0002/01/0911:09:0000:09:00 202/01/0911:00:0002/01/0911:04:0000:04:00 202/01/0902/01/09 103/01/0903/01/09 303/01/0903/01/09 604/01/0904/01/09 204/01/0913:09:0004/01/0913:12:0000:03:00 104/01/0913:02:0004/01/0913:15:0000:13:00 505/02/0905/02/09 total 1/200:07:00 In the example I am just wanting to work out the total time when both device 1 and 2 were down at the same time, above the total would be 7 minutes because for 4 minutes on the 2/1/9 and 3 minutes on the 4/1/9 they were down at the same time.
View Replies!
View Related
Time Calculation
I have a time card sheet that I want to make automatically calculate the time I have worked. It is set up with four columns that can't be changed. The first column is "Time In(TI)", second column "Time Out(TO)", Third column "Time Taken For Lunch(TTFL)", and Finally "Hours Worked". I have no problem getting the formula to calculate the difference in "Time In" and "Time Out" but taking the 30 minutes out of that is causing a rift. If I put the 30 minutes in 00:30:00 format it will take it but it thinks it's 12:30:00 and it displays in 00:30:00 which I want it to display 30. Just to clarify, I am using =(TI-TO)-TTFL in the Hours Worked cell.
View Replies!
View Related
Calculation Of Time Formula
I am trying to calculate the response time between when a phone call comes into my workplace and when the responder calls back...I have created a formula that does this using the times and dates of when the calls were recieved and went back out. This works except when the call comes in on one day and goes back later in the day the next day, making the response time larger than 24 hours. I also have it set up to eliminate 15 and a half hours from the calculation because our place of business is not open during this time. Further details.... The formula currently being used is... =IF(G50=E50, H50-F50+(H50
View Replies!
View Related
Time Spent Calculation
I need to find out the total time an issue was 'open' (not resolved) during business hours. Issues come in and resolution time is based off of a priority. Priority 1 should be resolved in one hour, Priority 2 should be resolved in 4 hours, Priority 3 should be resolved in 1 business day (8 hrs), Priority 4 should be resolved in 2 business days (16 hrs), Priority 5 should be resolved in 5 business days (40 hrs), Priority 6 should be resolved in 10 business days (80 hours). I have start dates/times field in a column (mm/dd/yyyy hh:mm) , a resolution dates/times field in another column (mm/dd/yyyy hh:mm) and a priority in another column (1-6). I need to calculate the time an issue is in open status (not resolved) based on it's priority but I need for it to take into consideration business hours (Monday thru Friday...7 am to 7 pm)...and not calculate time outside of 'business hours'.
View Replies!
View Related
How Do I Reduce Calculation Time
I am a big fan of huge files (30MB+) all interlinked with array formulas ...and lots of them. The problem is...one calculation take literally 5 hours. (I have one file I try to calculate since cca 4 hours now...it's at 63%) I keep getting extra memory with no visible improvement on the processing time. Is there a trick out there that I can use? Something is clearly wrong. I feel I will soon have to give up Excel, or my formulas, or my job ...
View Replies!
View Related
Formatting With Time Calculation
I have a calculation whereby I use two variables which are 1. Man hours left in the week formatted as [hh] and 2. Amount of work left to do in the week formatted as 0" Man Hrs" I need to carry out a calc which says:- Man hours left in the week - Amount of work left to do in the week. The answer should tell me if I have a deficit or surplus of hours which can then use for planning purposes. Unfortunately the problem I have is that I can't make the formatting the same for both unless someone knows of a workaround. Man hours left in the week = 128. Man hours required to complete all work this week = 224. Therefore I need to say 128-224 = -96. However when I do this this an answer of 16 is returned.
View Replies!
View Related
Textbox Time Calculation
I'm attempting to calculate the total number of hours between a start time and end time using textboxes on a Userform. Here is my latest attempt: Private Sub D24_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim myvalue As String Dim myhour As String Dim myminutes As String myvalue = D24.Value Select Case Len(D24) Case 1 myhour = "0" & Mid(myvalue, 1, 1) myminutes = "00" Case 2 myhour = Mid(myvalue, 1, 2) myminutes = "00" Case 3 The above codes are an attempt to force an entry as time (7, 07, 700, 0700=7:00 / 13, 1300=13:00 etc.). However, the result is not correct. For example: Start Time in Textbox(D24)=07:00 End Time in Textbox(D25)=17:30 Result in Textbox(D27)=0.4375 Desired Result in Textbox(D27)=10.5 I've never calculated times in textboxes,
View Replies!
View Related
Subtotals And Time Calculation
I am trying to calculate the time spent on each task. I have column A with Task IDs and column B with Time. Column A could have the same task ID repeated but the column B has the time for each entry. There is only start time for each task. Where the Task is repeated, want to know how many times the task has taken place and overall time spend on one task ID, calculating from the first entry to the last entry.
View Replies!
View Related
Time Calculation With Colored Cells
I was looking for a solution through the excel questions but could not find anything which would help me. As you can see in column B are two different colored Sun, red and magenta as well as Mon and Tue. I would like to find a formula or macro without creating an extra column which looks for the color magenta i.e. if the particular cells are magenta (Sun, Mon and Tue) then perform an action. I cannot use the formula in K7. ******** ******************** ************************************************************************>Microsoft Excel - Time Calculation with color cells.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)boutE4F4H4K4E5F5H5K5E6F6H6K6E7K7E8K8K9E10F10H10K10K11K12E13F13H13K13E14K14E15K15E16K16E17F17H17K17E18H18K18= ABCDEFGHIJK1Month: 2Working - Hours 15% 35% 3DayWeekdayStartEndHoursStartEndHoursStartEndHours414Fri20:000:0004:0020:000:0004:00 515Sat0:006:0006:000:006:0006:00 615Sat20:000:0004:0020:000:0004:00 716Sun0:006:0006:00 0:006:0006:00816Sun19:000:0005:00 19:000:0005:009?? 1022Sat20:000:0004:0020:000:0004:00 1123Sun0:006:00 0:006:0006:001223Sun19:000:00 19:000:0005:001324Mon0:005:0005:000:005:0005:00 1424Mon20:000:0004:00 20:000:001525Tue0:006:0006:00 0:006:001625Tue19:000:0005:00 19:000:001726Wed0:005:0005:000:005:0005:00 18 54:00 28:00 22:00Timesheet [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
Calculation Time And Charge Rates
I am trying to figure out a formula to figure out how much to charge for hrs of a rental. There is an automatic $1000 charge regardless of time used. the rate chart is as follows: $1000 + hourly charge = total =<50 hrs= $15/hr >50hrs but <100hrs= $6.50/hr >100hrs=$4.10/hr ex: so is A1= 200hrs then i want B1 to equal $1820.($1000 + (200 x 4.1))=1820. I have an idea on how to write the formula, but i am having a little bit of trouble with it.
View Replies!
View Related
Time Calculation - Ignore Weekend
Please see attached Excel File. I would like to calculate Column D depending on below criteria. 1. if both date is same than normal Column C-Column B. 2. if both date is not same than time diifrence should not be count after 17:30 to next day 08:30. 3. I do not want to include weekend (SAT & SUN) time diffrence if both date have. In column D I have filled two cells that I want by formula.
View Replies!
View Related
Calculating Time, Timesheet Calculation
I am working on a project involving calculating time. It is a timesheet calculation. I was able to design the following layout: .....A............B..........C..........D.......E.....F 1....Date.........Time IN....Time OUT...Hours... Total 2....01/01/07.....1830.......1930.......01:00...01:00 3....01/02/07.....1930.......2330.......04:00...05:00 4....01/03/07......830.......1900.......10:30...15:30 5 Column A is formatted for DATE. Columns B and C are GENERAL. Columns D and E are DATE format customized as '[hh]:mm' The formula to calculate the time difference between the numbers in column B and C is located in column D. It is as follows: =IF(C4<1000,TIMEVALUE(LEFT(C4,1)&":"&RIGHT(C4,2)),TIMEVALUE(LEFT(C4,2)&":"&RIGHT(C4,2)))-IF(B4<1000,TIMEVALUE(LEFT(B4,1)&":"&RIGHT(B4,2)),TIMEVALUE(LEFT(B4,2)&":"&RIGHT(B4,2)))..................
View Replies!
View Related
Reducing File Size
I don;t know if there is a way around this but we have a Excel workbook that holds around 14 worksheets taht is getting too large. It currently is around 2.5mb and we constantly use it as a template which is completed and emailed. Sometimes we have to email 4 of these all together and so teh email is too large, even zipping them has next to no effect on the size. The sheet that seems to causing the problem is one that has around 500 lines of formula across 20 columns. Most of these fields have conditional formatting and its this taht is boosting the size.
View Replies!
View Related
Converting Long Time Calculation To UDF
I work with a lot of time based worksheets for a 24/7 operation and constantly use the formula: =IF($E17>$D17,(HOUR($E17)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)),((HOUR($E17)+24)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60))) to calculate times worked where $D17 is start time and $E17 is finish time. Shifts can start and finish the same day ($E17>$D17) or go past midnight ($D17>$E17). To make matters even more interesting, there are a number of variables that require adding either "and" and/or "or" conditions to this formula so I can end up with several of these nested and it gets pretty complicated. I'd like to have a UDF so I can just enter the formula: =CalcTime(Start Time,Finish Time)
View Replies!
View Related
Reducing A Date To A Single Number
I would like to take a date and reduce it to single number. Eg. Reduce the day to a number. Reduce the month to a number. Reduce the year to a number. Add the 3 together. This gives you a number. Reduce this number to a number. e.g. today's number date 30 / 03 / 2007 Day is 30 Month is 3 Year = 9 Sum of these is 15 which reduces to 6. 3+0+0+3+2+0+0+7=15 1+5=6 Basically 9 is the highest number. The number 13 would be 1+3=4 and so on. The idea is to return a single number between 1-9.
View Replies!
View Related
Reducing Number Of If Statements In Code
i have a simple if statment If cboname.Value = Sheets("Data Sheet"). Range("E2") Then txtpay.Value = Sheets("Data Sheet").Range("F2") End If this need to be done for a large list of names and input into another sheet. what i wanted to know was if there is a quicker way of doing this code without repeating the code loads of times
View Replies!
View Related
Date/Time Calculation For WeekEnds/After Business Hours
What calculation would I enter in a results cell if I wanted to find the delta between 2 times in date format that repersent just the business hours of 8am-5pm, therefore excluding after hours and weekends. eg. Date 1 Date 2 Result Time 23/07/07 8:00 24/07/07 14:55 15:55 So far I can't get the caluclation that will compensate for the after hours and week ends.
View Replies!
View Related
Time Calculation: Calculate How Many Hours Have Elapsed Between To Entries
I am trying to build a spreadsheet to calculate how many hours have elapsed between to entries; start time (H10) e.g. 9:15 AM and end time (I10) e.g. 12:15 PM. The formula that I am using in the calculation cell field (J10) is (I10-H10+(I10<H10))*24. This formula works great till I wish to include in an IF statement. What I would like is if the total hours calculated with the formula (I10-H10+(I10<H10))*24 is less than 4, return 4 (hours) otherwise the value. As well if there is no start time nor end time entered then return zero.
View Replies!
View Related
Omit Worksheet From Calculation
Broadly, my workbook contains 5 worksheets. Worksheets 1, 2 and 3 contain calculations, worksheet 4 contains a summary of calculations from worksheets 1, 2 and 3. Worksheet 5 is a data table used for worksheets 1, 2 and 3. I need the worksheets to calculate in the following order: 1. Worksheets 1 and 2 extract data from worksheet 5. 2. Worksheet 4 captures that data. 3. Worksheet 3 uses the data from worksheet 4 and extracts data from worksheet 5. 4. worksheets 1 and 2 recalculate using the calculation from worksheet 3. 5. Worksheet 4 captures the refreshed data from worksheets 1 and 2. Is there a way, using formulas in the worksheets, to ignore worksheet 3 on the first iteration of worksheets 1 and 2, and prevent recalculation on the second iteration of worksheet 1 and 2? In my mind I can see Excel simply recalculating over and over again, or will it stop. Is there actually a "problem" here or am I perceiving something that will not occur?
View Replies!
View Related
Delay Calculation Of Worksheet Functions
I have a workbook that has many simple functions moving data around to different sheets and processing some if statements on some of the data. The problem that I am running into is all of the data is initially pulled in from a SQL DB, and I believe that the workbook is calculating many of it's if statements, prior to all of the SQL data being loaded, therefore, many of the worksheet formulas are coming up with the wrong result. Is there a way to delay the calculation of all the basic worksheet functions, so that I can ensure that all the data is in the workbook from SQL, before they all fire. I have attempted to use Tools--Options--Calculation--Manual Calculation, but it appears that even though I have things set that way, all of the formulas in the workbook have already pulled their values through.
View Replies!
View Related
Worksheet Calculation Triggered By Drop Down Box
I am trying to do is trigger an event when a cell changes, and this cell is updated via a linked drop-down box. The issue is that the code seems to work fine (in about 10 seconds or less) if a manual calculation is entered into the sheet - but when it's done via the drop-down box, it takes about 3 minutes to complete the macro.
View Replies!
View Related
Multiple VBA Calculation Events To Just One Worksheet
I use VBA's WorkSheet_Calculate event to detect when a cell of particular interest calculates to a specified value. It works well as long as there is only one worksheet (tab). However, when I duplicate that tab (and the final app could have up to 10 copies) to run a different set of data concurrently, I get bad results. The data is online, real time trading data, with each selected stock being tracked in a different tab. Apparently both (or all) tabs in the workbook react to the same event, whereas I would assume that the event routine in each tab would react only to a calculation in that tab. If this is in fact the case, is there a way to a) make the event routine in each tab respond only to a calculation in that tab, or b) upon a calc event anywhere, determine which tab it occurred in? Here is the present event code; right now it reads exactly the same in every tab. As long as nothing is happening in any other tab, it does its job correctly.
View Replies!
View Related
Setting A Single Worksheet To Manual Calculation
Is there a way that I can set a single worksheet in a workbook to always be on manual calculation, but keep all other sheet in the workbook set to automatic? I want to be able to open the workbook, any calculations to perform automatically and then i will select the 'manual' sheet and perform these calculations manually. I also need to these settings to always apply each time i open the workbook.
View Replies!
View Related
Adding Rows And Calculation To Macro
I am extending rows on a salary schedule. As you can see below, the rows include: Bargaining Unit (GP) Schedule(2A) Range(5) Step (A) Hourly (S) and the hourly wage(11.52) GP,2A,5,A,H,11.52 GP,2A,5,B,H,11.82 GP,2A,5,C,H,12.17 GP,2A,5,D,H,12.5 GP,2A,5,E,H,12.88 GP,2A,5,F,H,13.22 GP,2A,5,G,H,13.61 GP,2A,5,J,H,14.18 GP,2A,5,K,H,14.71 GP,2A,5,L,H,15.26 GP,2A,5,M,H,15.83 The Steps currently go from A thru M but I need to add N thru Z and after step M there is a 3.75% increase for each step. You can see that this is a csv file (comma delimited). All of the other info needs to autofill and only the new steps (N-Z) will change and the wage for each of the new steps. The last row is 2 decimal points.
View Replies!
View Related
Failure Of Automatic Calculation When Worksheet Moved To Another File
I have workbooks in which summary tables are generated by lookup formulas. Tables are on different sheets but all use the same lookup value by referring to a cell on the 'master' sheet. The lookup value appears in cells on all sheets, by reference to the master sheet (e.g. [formula] = mastersheet!$B$2). The master sheet contains the main summary table and is copied and detached for distribution, using a macro. For practical reasons, this is done in two stages, first copying the sheet within the workbook (to make minor alterations), then moving it to a new book to save and distribute. This is where the problem arises. After the sheet has been detached, we find that if we now change the lookup value on the master sheet in the original file, the tables on that sheet will update normally, but the cells on other sheets remain frozen at the previous value and the tables on those sheets do not update. The only way round the problem is by Shift + Ctrl + Alt + F9. (Maybe I should add that all my workbooks are always set to automatic calculation.) The macro itself is not the cause of the problem; if we follow the same procedure manually, the result is the same. However, if we move the sheet in one step, eliminating the intermediate copying stage, the problem does not arise. But this is evading the problem, not solving it, and I would be reluctant to have to resort to this. The original problem remains as stated, viz. failure of automatic calculation.
View Replies!
View Related
Copy Values To Another Worksheet For Calculation & Return Result
I have wind speeds and elevation data for 200 points. Each variable needs to go into a calculations spreadsheet on another worksheet. Once each variable gets put in the calculations automatically spit out the result. Now I need to use macro to automate this process so that it will repeat the steps I took for every line of data. In English code terms, I want the Macro to say, take wind speed number and input in wind speed cell in calculations spreadsheet. Then take elevation number and input in calculations spreadsheet. Then take result number from calculations spreadsheet and input in cell. Then loop to go down the 200 lines of data. Is this possoble? Sorry for the non-technical wording.
View Replies!
View Related
Rows(":").Select: Calculation Within The Rows Collection
I want to use a calculation within the Rows collection, but I get a type mismatch error. I realize it boils down to syntax, but I don't know what I don't know. 1. In place of, say, Rows("2:37").Select...I want to use Rows("2:x-2").Select where x-2 is a simple subtraction calculation. I also want to use two variables. I'd hope to be able to infer what to do from above, but just in case: 2. Rows("z:x-2").Select
View Replies!
View Related
Date/Time Formula: Pick Up A Date With Time Entry On A Worksheet And Place It Into A TextBox On A UserForm
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the Private Sub UserForm_Initialize() If Not Range("dDate").Value = "" Then TextBox2.Value = Range("dDate").Value TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM") Else TextBox2.Value = "" TextBox2.SetFocus End If End Sub "dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
View Replies!
View Related
Vba - Cell Copy And Time Variable
Into Sheet1 there is in A1 (or should i call it better R1C1) a value which is update every sec from i timestamp cell. If the value of this cell change i want to put the previous value into A1(can be another) cell of Sheet2 and keep the new value into A1 of the Sheet1 When the value change again i'd like to put it underneath the cell A1in Sheet2, so that i can take a history of the values. So there will be a column A with different rows. Every 3 mins i want change the column into Sheet2 so from A now the values will be update on column B Everything will start when i open my excel file, because the timestamp will start to run. What about if i want to align the change of the column with a particulary hour? for instance if i open my excel file at 9.33am everything will start since that time, but what if i want make it start from (a multiple of) 9.00 am (so every 3 min from 9am?
View Replies!
View Related
|
TRACKER
