I'm struggling to include a repeating 8 (or 4) weekly cycle in my sheet.
For instance, week 21 starts on 20-05-2013, than I would like to have the below cells filled untill 26-05-2013, following cells must be blank, untill 8 weeks are over, and the first date you see again is 15-07-2013, and so on. Using the MOD formula, I can get the first day of each cycle correctly, but I'm having difficult to get the next 6 cells entered and the next ones blank again untill 15-07-2013
I'm using now following formula, where B is the date:
Code:
=IF(MOD($B$264-B264;56)=0;B264;"")
but I need to nest another IF I guess that takes care of the rest.
I'm try to do a what IF Formula, so I can automatically add the cost into the right year for a lifecycle cost
My criteria is in column K I have either New Build or Existing and column AT I have year 12 of the lifecycle and column AZ I have year 18
And what I want the result to say is IF Column K Say New build I want the value of the first maintenance year to start in year 18 and column k say Existing I want the value of fist year Maintenance to start in Year 12
I already have this formula in the Column AT&AZ working out he value
I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.
I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
I want it to show me that if B3 is bigger than any of the other cells that you should "eat less". It works if a number is entered into every cell, but if a 0 is entered into one of them it comes up with "eat more" when it should be "eat less"
I'm calculating a variance for a set of actual numbers vs. variance numbers. When there are no actuals, I don't want the formula to calculate a variance. How can I do this?
=SUMPRODUCT(--(GLCodes=$B7),MasterNetAmt_01) I want to dis-include the CC="06" in the summation. I tried using: =SUMPRODUCT(--(GLCodes=$B7),(cc="06"),MasterNetAmt_01)
I have a formula to pick up certain bits of information depending on the contents of a cell.
=IF($F3="138-1",MID($E3,11,4),IF($F3="151-26",MID($E3,11,4),IF(MID($F3,1,2)="00",$E3,IF(MID($F3,1,2)="40",$E3,IF(MID($F3,1,1)="-",$E3,IF(MID($F3,1,1)="8",$E3,$F3)))))) The data in column F has a mixture of 8 digit numbers including numbers starting with multiple zero's, the numbers 138-1 and 151-26, then some text.
Orginally the 8 digit numbers only started 00 so it was easy to pull out the relevent information, then they started 40 and then 8. I'm just worried that I'm going to end up with too many nested 'if' statements (and reach the max limit), so I was wondering if I could do 'any 8 numeric digits' instead of this current 'dead end' solution.
I'm not sure if I could use a wildcard of some sort with the MID formula
I would like a cell to show either OK or No depending on the requirements of three other cells as below
G4 is the cell to show a value. For OK a date in G6 must be more than todays date, a tick must be in F6 and a date in D6 must also be more than todays date.
I can get G4 to do each section individually i.e. (A4 has todays date in it, updating automaticaly)
I have been trying to come up with a formula that calculate the number of times one of my employees has taken sick leave. The instances do not mean number of times they have taken a medical leave but the number of instances. For ex: In the month of Sept, my employee takes a medical leave on 1st Sept, then another on 7th Sept and then 3 days of medical leave on 12,13, and 14th Sept. So the total medical leaves is equal to 5, but the number of instances is 3.
The formula should be able to provide a result of 3.
I am needing to count the number of calls in a call center between certain hours by date. Column A is the date, Column B is the time of the call. I am needing to know how many calls occurred on X date between the hours of y and z. I have tried various forms of the countif and sumproduct but can't seem to get any of them to work for me. Here is the graph I am trying to populate:
I am having trouble trying to develop a code to include into a spreadsheet for work. It will be a live spreadsheet accessed by a few people who will have control over there own columns in the spreadsheet (2 columns are designated for one project) Each Project director is to edit the info about their project.
So my goal is to put a code in cell C3 that shows the date that cells C4-32 and D4-32 were last updated.
I have a number of records that have in one cell a string of the form 1,2,3 etc (up to 10) representing conditions that have been met. There are a number of options available for producing reports on all of the other attributes in a record but now we wish to add this element as well.
The reports I can produce using VBA and the INSTR function, however on the summary page where, a total of all records matching available criteria is shown, depending upon drop down items being selected, I need to add this element to the equation as well. The existing conditions are counted using SUMPRODUCT and a combination of IF statements and work fine.
As there is an option to create a report for both AND and OR, e.g. if condition 1 and 2 and 3 apply include in the report, or if condition 1 or 2 or 3 apply include in the report
The conditions are selected using a check box and a drop down list to select the AND/OR condition.
I have been trying various combinations of database functions, countif, find and cannot get them to evaluate. I suspect that it may be beyond my reach to use a formula and I will need to use VBA with a Worksheet_Change macro to achieve what I want, or alternatively redesign the layout to store the conditions differently, however if anyone has any ideas for a formula I would much appreciate hearing from you.
A sample worksheet illustrates how the data is currently shown. The string in the record column is created using VBA and then inserted into the actual record.
I have a row that contains a different time (hh:mm) in each column where each column represents a different date which is display in row 3. If there is no time the cell is populated with "N/A"
I want to count how many cells for a specified Month/year are not equal to "N/A". I have been able get each selction criteria to work but when I try merging numeric and non-numeric queries I lose it.
The following formula was able to give me the count of cells "N/A" =COUNTIF('Master Data'!$UA59:$ALZ59, ">0")
and this gave me a count by Month/Year =SUMPRODUCT(--(YEAR('Master Data'!$UA3:$ALZ3)=2012),--(MONTH('Master Data'!$UA3:$ALZ3)=1))
When I tried creating one COUNTIF I was unable to get it without an error as I needed the YEAR/MONTH functions.
I'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.
I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.
I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.
I'm looking for the easiest way to count the number of occurrences within a cell range.
The formula that I'm currently using is:
=COUNTIF(D$5:D$8,"a*")
This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;
When I want to find multiple values in the range and count them all, I use this formula:
Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"
for example here i want to keep only gen, red and white columns only out of columns what i have in my data. I have so many columns in my original data but here i given just small example. How to proceed with macro or any other way because removing manually taking long time for me.
I've recorded a Macro that goes through multiple spreadsheets and refreshes the pivot tables in the sheets. I would like the Macro to run without it actually showing it go to each sheet and showing the pivot table updating. Bascially I would like the spreadsheet to remain on the first sheet while it's working with all the spreadsheet in the workbook when the macro is running.
I have a table, 2 columns by 10 rows (A1:B10). The table values are the result of calculations (imagine they are random). These calculations are performed repetitively for some specified number of repetitions (let's say 10 iterations). (e.g. all of the table values change with each iteration until the 10 iterations are complete. With each iteration all of the values in the table change). At the completion of the 10 iterations (one full cycle) I want to know the minimum and maximum calculate value as the 10 iteration cycle was performed.
To simplify; the table changes 10 times in one cycle. I want to know the minimum and maximum values attained in the cycle.
It is easy to determine the minimum and maximum values of the table for each iteration. It seems the problem would be write some vba code that 1) found the min. and max. 2) save that value 3) find the min and max in the next iteration and compare the old and new values 4) retain the value or replace the min and max values with new values and 5) proceed to the next iteration until the cycle is complete.
So I have a cycle time formula; Start Date to Completion Date, if the task is not complete the completion date field is blank. In this case the cycle time is listed as a negative 5 digit value. The networkdays formula takes into consideration weekends and holidays.How can this field be left blank rather than the negative value?
I want to combine cells from two adjacent columns in this way: a1 is combined with everything in column b, then a2 is combined with everything in column b, etc. So that I have a1b1, a1b2, a1b3, a1b4, a2b1, a2b2, a2b3, etc.
I am trying to cycle ALL open workbooks so that I can choose the file to manipulate in subsequent code this is the code that I am utilizing furhter below
this is my code
VB:
For Each wb In Workbooks If MsgBox("Do you want to do access this Workbook for the Update " & Chr(10) & Chr(10) & wb.Name, vbYesNo) = vbYes Then wb.Activate VI_wb = wb.Name I = True End If Next wb
But for some reason it only loops through .xls and xlm workbooks BUT not .xlsx
is there a way that I can loop through any and all open workbooks.
The reason I do this is because my update are based on many numerous excel workbooks with differnet extentions, and naming conventions and many of the come via email, I cannot use the eact naming convention
