I have a worksheet in which i have rows with yellow colour. In that sheet I Have written formula in that cells & rows. But i want that as soon as the total (SUM) of yellow coloured row crosses value 20 ALL THE ROW CELLS SHOULD BE ZERO. How can i do this
I am attaching sample sheet. EXAMPLE15.xlsx
Example: I have Sheet 2 set to autocalculate on activate using vb. So when you click on that tab "Sheet 2" it will autocalculate.
My Question is, I have another macro that is copying information between Sheet 1 and Sheet 2. And I don't want calculation to occur during the macro but since it has to select Sheet 2 it performs the autocalculate. Do I have any options to prevent the calculation from occuring when I run my copy paste macro? I only want it to autocalculate when the user selects sheet 2
I have a report whose calculation time I've reduced greatly by turning off automatic calculation and including in the worksheet code directions to only calculate the impacted range. BUT, it still takes a lot of time to calculate on close, when I turn automatic calculation back on. I'm afraid my users will think their machines have crashed. Does anyone know of a way so that on close, the workbook does not calculate, even though Calculation is turned back on to automatic?
Private Sub Workbook_Activate()
With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With
End Sub
Private Sub Workbook_Deactivate()
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
End Sub
I have a percentage calculation that I need to ensure excel does not round up the last digit.
My calculation is 2463000(cell:I13) divided by 257000(cell: I14+I15)
Excel calculates this value to be 0.958365759
However I need it to display and re-use only 0.9583657 as my calculation has to be precise to 7 decimal places.
If I use the formula =ROUND(I13/SUM(I14+I15),7) then excel returns the value 0.9583658 - it rounds the last digit up.
I have tried to use the option "Set precision as displayed" and set my decimal places to 7 but this still sees my 7th digit rounded up.
How do I set it so the 7th digit is not rounded up.
My original formula is =IF(AND((L3+L8+L13)<=O3,(L3+L8+L13+L18)>O3),((O3-(L3+L8+L13))*I18/L18))
I have updated it to be =IF(AND((L3+L8+L13)<=O3,(L3+L8+L13+L18)>O3),((O3-(L3+L8+L13))*(ROUND(I18/L18,7))))
This formula is being used in a vacation time spreadsheet. So after each paycheck the if statement is applied and need to calculate the accumulation for that week based on if the person has worked 5 yrs or less. I need to stop the today's date calculation in the if statement after it is used. So that each time the spreadsheet is opened the values are a fixed number not changed based on the current date. However if the if statement is pasted into the next pay week it will calculate based on the actual date.
It is calculating is the following:
C4 = 5yrs Anniversary Date of Hire
3.08= less than 5 yrs
4.62= more than 5 yrs
=IF(($C$4)<=TODAY(),4.62,3.08)
[URL] ......
I have a file that tracks orders placed.
Column A gives the request date of the order, based on the TAT in column B the projected dispatch date gets calculated.
Once the order is dispatched and the date gets updated in column Z, the redemption status in column V gets updated as "Dispatched within TAT" or "Dispatched out of TAT" based on whether they have met the TAT or not. If the dispatch date is left blank then it shows "Yet to be dispatched".
My problem is that the column D does not stop calculation even after the order is dispatched. And therefore because of the conditional formatting turns red the minute the number is more than 0. So it is misleading.
I have a notepad with lot of information's but when i try loading those info's to excel I am getting message "File Not loaded properly"
Is there a VB code which downloads it to excel and automatically moves to next tab and paste the rest.
I have a IF formula that check if “F13” cell is "+" if not do calculation (F13-E13) and stop, if yes go to the previous line and check if the “F12” is "+" if not do calculation (F12-E12) and stop, if yes continuo to the previous cell and so on until you find cell without “+”
The EXCEL IF formula is
=IF(F13="+",IF(F12="+",IF(F11="+",IF(F10="+",IF(F9="+",IF(F8="+",E8-F8),F9-E9),F10-E10),F11-E11),F12-E12),F13-E13). I'm looking for VBA code with FOR to run and do the same without the limitation of 7 if inside if.
i didn't realize is that my current funcation that calculates vacation hours... will increase after a new year. i'd rather have it not increase until they are "reset" for the new year. how do i stop the function? =VLOOKUP(DATEDIF(A8,TODAY(),"y"),$S$8:$T$10,2)
basically goes to a lookup table with the caculations. PROBLEM: if a user's anniversery date passes, they may go from a 1-2 year status to 3 year bump... this will auto adjust the amount of vacation hours they have. if the reset button is ran to calculate the vacation hours, it might over calculate giving the user 40+ extra carryover hours. anyway i can make this vlookup stop when the current date is or has passed the anniversery date, yet has not been reset? maybe a count down timer, not sure. http://www.ozgrid.com/News/excel-eva....htm#ExcelTips
I am comparing a set of common variables across 2 sets of independent financial data. When comparing a variable across the 2 sets e.g. profit I want to colour a neighbouring cell based on the size of the difference. For example if the difference between the two values is within plus or minus 1 make it green, plus or minus 5 red etc.
View 5 Replies View Relatedmy excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
View 4 Replies View RelatedI have cells, or columns hidden when I do not need them, but I have to a data entered in those cells to make my spread sheet work. Usually just 0.
When I do the all clear or " clear all contents" operation at the end of the day, it clears the hidden data/cells too. Is there anything I can do to stop excel from clearing those hidden cells?
I have a master sheet and 20 sub sheets. I input data in the master and it filters through to the 20 subs and calculations are done. the problem is that on the master there are blank cells, that need to remain blank cells until i put the data in. This data is copied to the subsheets using a =sheet1a1 type of formula. what happens is when a blank cell is copied it appears in the new location as a 0. I need this to remain blank as the 0 causes calculations that I don't need and mess up a lot of the data I need. Is there a way that when it copies the new location remains blank until the data is inputed in the master sheet?
View 7 Replies View RelatedI am trying to compare a value ( say C80 ) and figure out how many cells it takes to exceed a value ( say -2 ) in a column ( say E79:E2 ) but then actually stopping the count after that first cell count has been triggered.
I'm in a bind - any help would be highly appreicated.
I have a sheet set up with invisible values in certain cells. To make them invisible, I have coloured the font the same as the backround. The colour used is a light shade of yellow, colour index 36 I think.
Unfortunately, even though the values in these cells are invisble on the screen, they are visible when the sheet is printed. Is there any way to stop these cells from being printed? Note that they are scattered all around the place.
i am creaing a chart in excell but i have a problem. i would like to have a chart like this one http://shrani.si/?screenshotow31.jpg. but when i create my chart it looks like this http://shrani.si/?screenshotow2t.jpg. first chart is created with some macro so i dont know how is it working but i think the problem is in blank cells because if cell is blank the chart line will go to 0 but i dont whant that :s
View 2 Replies View RelatedI have attached excel sheet. Column A consists of List of LAN numbers, Column B consists of List of Documents tagged & Column C consists of time at which particular document is tagged.
I need to calculate the time taken to tag all the documents in Lan1/Lan2/Lan3 in Column D. (I can ignore the date by doing text to columns).
I need a formula that I can place in, lets say, Cell A1 that will reference Cells B1, B2, B3 and C1, C2, C3. These all have numbers & %'s in them. However, I want to "weight" the B cells by the percentages in C cells. The problem I am having is that when I change the %'s nothing is changing in the output...almost like I was doing nothing but averaging B1:B3 without the % to adjust.
This is how I envision it, but my calculation may be wrong of course :
A1 = average(sum(B1,B2,B3)*C1, sum(B1,B2,B3)*C2, sum(B1,B2,B3)*C3)
The Goal:
This would allow me to change the output depending on the percentage. So if I am looking at days to an appointment and the B cells represent areas of a city: B1 = 10, B2 = 5, B3 = 2 the average would be 5.6, however, I know that B1 city has more "weight" to it with our customer base so I want B1 to be closer to what will show as days out (which is obviously important as 10 days is not good). So I would assign C1 = 60%, C2 = 30%, C3 = 10%. This should pull the average more to 7 or 8 as it would lean more towards the B1 value. I hope this makes sense.
I did try going more of a route where I did something like =average(B1*C1,B2*C2,B3*C3) but that lowered all the original values instead and averaged them instead of pushing the number more towards the higher weighted % of B1 cell value. I can send an example spreadsheet if I am losing anyone.
I have a spreadsheet that is designed to aid a production department to complete and track their orders. I need two things from this.
1) I have an order of 500 baskets. Baskets are stacked, but the heights vary from 16 to 20. I need the spreadsheet to be able to work this out for me.
Example:
500 baskets 20 high = 25 stacks.
500 baskets 16 high = 31 stacks and 4 baskets.
Excel would work the last equation out as 31.25. How can I get excel to use the remainder (in this case 0.25) to work out the required number of baskets?
2) Using the above, I need the sheet to fill out a certain area with information. So, if the order is 500 baskets at 20 high, I need 25 of the cells in the specific area to display the number 20. Likewise, if the order is 500 baskets at 16 high, I need 31 cells to display 16 and the remaining cell to show 4.
I need to create a macro that will make a cell only run its formula when an associated cell has something in it that will trigger the function.
View 2 Replies View RelatedI am trying to code for the following conditions and will like to know how I can embed these conditions into one statement.
IF(AND(X>365, A>B), C*D
IF(AND(X>365, A<B), E*F
If neither of the conditions apply, then 0.
how to stop rows resizing with text which paste into cells
View 1 Replies View RelatedI have a VBA code that when activated it copies 6 cells from sheet1 to the next available row in sheet2 - A2:F:2.
alerting and stopping the routine if any one of the cells in sheet1 are empty so the user has to input into all cells before the routine will execute.
does it need a check cells for content if error highlight empty cell.
We were so close!. But it appears that the assumed correct answer only works if there are no repeating N. The repeating N gets the same count as the last Y and it throws off the sum ....
View 14 Replies View RelatedI have a data validation list in col A.
I don't want ppl to be able to paste values in cell - them must either type the data or select from list.
Also - the sheet is protected but col a is open
have tried...without success
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.CutCopyMode = True
End If
End Sub
I'm protecting a spreadsheet with Excel 2000. For that i used the Excel functionality : Data -> " Validation"
Unfortunately, I can not protect the all sheet or workbook (Requirement). So even if the cells are protected by "Validation", any user can delete the cells using the key DELETE or BACKSPACE on the keyboard !
VBA Macro (which could be activate at each change on the sheet for example...) ? or if it's possible to avoid cells deleting with the "Validation" in the Excel Data menu ?
If it's a macro, it would do :
1 ) see if a user is pushing DELETE or BACKSPACE on the keyboard
2 ) see if the cell contains formulas
3 ) if yes, make a box appears to say that is not possible to delete this cell
4 ) return on the Excel sheet without changes
I don't want my sheet to auto calculate every time I change a cell that has nothing to do with the range I want to calculate. What would be the code to only have auto calculate run in range("A13:L17") when a worksheet_change happens in range("A13:L17").
View 9 Replies View RelatedI 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.
I`m working on a compression of a picture. For most of you that probably is pea-nuts, but I really just stopped wearing diapers when it comes to excel. At this point I have a sheet in front of me with 300 rows (length of the picture) and 185 columns (breadth of the picture). The cells are pixels and contain values ranging from 0 to 255.
I now need to do an operation I simply can't figure out. I need to select four cells at once (A1:A2 and B1:B2) and replace A1 by the average of these cells and the other three cells should be emptied during that operation. This operation I should do for the whole sheet. In the end I should see a chart with values in every two cells (A1, A3, A5 ... for the first row) as the other cells will be emptied
i have a sheet which has name of students (A2:A10), B1:E1 has project name. and the projects are catagorised by color red=high,green=medium, yello=low. i have given weightage to these color red=80%, green=50%, yello=20%.
similarly the involvment of the students in each project is shown by these three color in respective cell. like B2 is red, C2 is red,D2 is blank, E2 is green. (the weightage is same as above). in column F i want the total utilisation of each student, which is simply sum of product of weightage given to these colors.
e.g.
project 1project 2project 3project 4utilisationsam1.38joe0.54nick0.72
what i want is to calculate this utilisation number, but i want my sheet to be shown in the same color format.
i am sure that there is some solution for this.