If the result of the above formula is less than the value in cell BK2, then look if the number in B2 is available in sheet2 (another sheet) column B.
If the number in B2 is available in sheet2 coloumn B,then the result should be BK2.
If the number in B2 is not available in sheet2 coloumn B,then the result should be above mentioned formula which is =IF(NETWORKDAYS(MAX(BI2,DATE(2014,6,1)),MIN(BJ2,DATE(2014,6,30)))<0,0,NETWORKDAYS(MAX(BI2,DATE(2014,6,1)),MIN(BJ2,DATE(2014,6,30))))
I have a lot of data broken up into numeric decimal form in one column. I need all this data to be transferred to different worksheets based on the amount of decimals. Attached is some of the data I need sorted. The first sheet is my main sheet, then the next sheets need to be ascending in numeric order by sorting the data with two decimals under the column Tear number system.
For example I need the next tab to be the data with 0.21 with 2 decimal places, then the next tab would be the data under 0.28 with 2 decimal places, then the data under 0.29 with 2 decimal places. So the 0.21 worksheet would be 0.21 and have all the data for 0.21.1, 0.21.2 all the way through 0.21.6.
I also need this to be compatible with new inserts to the main table. Meaning if a new number is entered in, the number and data would automatically be entered and sorted to the tab that the number pertains to. I have tried a vlookup function but have not had much success. I have also tried recording a macro but am having problems with that as well.
I have 4 conditions in one formula, and only the first condition (when met) evaluates correctly. When the other conditions are met, I get a VALUE# error.
I want to concatenate 2 cells, based on whether there is an "a", "b", "c", or "d" in a particular cell. see example. Below is my formula:
My userform has a combo box that upon entry event is populated via a sub procedure.
The sub procedure defines a column of data on the active spreadsheet as a range and manipulates the range to add unique values only in alphabetical order into a collection.
Which the collection is then used to Add.Items to the combo box.
The userform allows selection of an existing value from the combo box OR type a new value In (no formatting on value).
When combo box is exited and a further command button is pressed the value from the combo box is added to the spreadsheet.
However, I don't need to refresh the source values of the combo box upon EVERY entry as I currently do, ONLY when a NEW value has been typed in and added to the spreadsheet.
How can I (possible check index value ?) ensure when the user types in a new value, exits field and presses the command button is the ONLY time when the combo box source values need to be refreshed.
I have two worksheets. one has a list of formulas which i manually enter or change as time goes on. the other spread sheet has the same list of formulas, but I just chose which of those formulas I want to use frmo the drop down list provided to me by using the data validation of the other page. Each row that I enter by selecting the correct drop down options, i would chose a product name alogn with it. so its either Service or Fix. The question i ahve is, is it possible to adjust the manually entered formula and only have the formula rows where the service words are in another column but same row updated?
I have a spreadsheet with ~35k rows of data. This isn't too easy to explain I have the following data (example).
User Name Employee ID Data Level Jan 08 Feb 08 March 08 etc. Jo Bloggs 12345 A1 1 1 0 Jo Bloggs 12345 A2 1 1 0 Mark Jones 89101 A1 1 1 Mark Jones 89101 A2 0 1
For each user with data level A1 I wish to add an X if the same user has a corresponding data level of A2 so the result should be,
User Name Employee ID Data Level Jan 08 Feb 08 March 08 etc. Jo Bloggs 12345 A1 X X 0 Jo Bloggs 12345 A2 1 1 0 Mark Jones 89101 A1 1 X Mark Jones 89101 A2 0 1
I would really love a formula or macro to be in existance that would save me from trawling through all this data and going blind.
I have a list of employees who's job status may change at the end of a given month. If the status changes, they have a supplemental payment that is tied to their job status and may change as well. But that depends upon certain conditions like their years of service and their union.
I'm trying to write a formula that will update that supplemental payment based upon certain conditions, but I can't quite seem to get it right. A challenge is the table I'm looking values up in has 8 columns and the value could be in any of the columns depending on years of service.
These are the conditions for the lookup: 1. If their union local is the same, they would keep the supplemental payment so long as it is not more than allowed for the title. If it is more, they get the supplemental that corresponds to the new title and the years of service.
2. If the new title is PAA and they have 10 or more years of service, the new supplmental is $600.00
3. If the new title is Clerical Associate II and they have a 6 or more years of service, they get a supplemental that corresponds to their years of service.
I have attached excel with the logic I am using, but I have a problem and needed logic to update to make sure it is not inserting same data (duplicate data).
Please update the logic so that if it is having same data in sheet 3 it should not insert or delete duplicate.
Code: Public Sub CopyData() Dim ws As Worksheet, bi As Byte, vData(1 To 9), bi1 As Byte, vData1(1 To 9), bi2 As Byte, vData2(1 To 9), bi3 As Byte, vData3(1 To 9), bi4 As Byte, vData4(1 To 9) Set ws = Sheets("Sheet2") For bi = 1 To 9
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
Let's say that in column A I have numbers,"Yes" and "No". I want in column B to have only the numbers from column A, in the same order without any empty ranges, and everytime I add in column A a new number, column B to update automatically with that number. Let's have an example:
A B Yes 12 12 13 No 10 13 No 10 Yes
And if I want to add in column A: A B Yes 12 12 13 No 10 13 25 No 15 10 Yes 25 15
So the column be will update automatically. I already tried =IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROWS(B$1:B1))-ROW($A$1)+1),") but using this many times get's my file very heavy and the excel is working slow.
I have some VB code which sequentially opens over 200 workbooks to extract data from each and populate another workbook. These workbooks do have links to other workbooks in them. For some reason when some of these workbooks are opened I get a requestor window asking whether I want to Update or Don't Update the data. I always want to Update the workbook and believe this can be done in VB by hiding the requestor?
I have a vlookup to another workbook. It works fine if both workbooks are open. But if both are not open and I open the workbook with the links and click Update, #VALUE! returns. I have attached the two files. I don't think it is my formula, but here it is anyway. =IF( COUNTIF([Tempozgrid.xls]June!$A$52:$A$83,A3),VLOOKUP(A3,[Tempozgrid.xls]June!$A$52:$L$82,12,FALSE),0)
I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'! D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?
I have been trying to remove or disable the message on update to other worksheet " To update all linked click yes......" I have try the following unsuccessfully
Sub auto_open() Application.AskToUpdateLinks = False End Sub
On the menu bar choose Edit ---> Links Can not choose manual, as the option is grey out (disable) I would prefer a vba solution, but I am open to anything to get rid of this message
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem ... .Fields.Item(1).value = activecell 'activecell value = "Joseph" If Not isempty(activecell.offset(0,1)) Then .Fields.Item(2).value = activecell.offset(0,1).value Else .Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works End If ... End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty ' or .Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I want to calculate the sum of column with two conditions on different worksheets,
Example Data Stock #QTYMonth 01335208Aug-06 01337402Aug-06 01337392Aug-06 01335400Sep-06 01337100Sep-06 01337102Sep-06
If the stock # is equal to the stock # and Month equal to month I need to get the result in another sheet as below. Stock #Aug-06Sep-06 01335208400 01337794202
what would be the most effective and easiest formula to use if I want to sum a range of numbers meeting 2 conditions (or more).. eg.. sum all the unpaid invoices (condition 1) due on March (condition 2).
I have the following code: =SUMPRODUCT(--($A$2:$A$400="OKC"),--($C$2:$C$400="AF33"),$G$2:$G$400)
It works great, but I am needing to now add a 3rd condition, or filter... In addition to it looking for "OKC" and "AF33" I also need it to sort by month. example; "March". I need it to be in code that works on old versions of excel as well...
Although I am not getting any errors, I am not having success returning proper answers with the formula below. Perhaps I cannot do what I am attempting.
where: K22:K169 are user entered categories from a drop down list G22:G169 are user entered dates F9 is a user entered update date column R are monetary values with first row being a revenue$ second row margin$
I need to summarize every other row (one row is revenue, one is margin) if the two conditions are met.
I'm (and you are too!) still helping to streamline a charity's spreadsheet. There are two worksheets that this question refers to: "DD Tally" and "List of DD Donors" (where DD stands for Direct Debit). In the DD Tally worksheet we are trying to summarise the information contained within the List of DD Donors. We would like to be able to estimate how much, in direct debit donation, we will recieve in the financial year (currently, April 06 to March 07). We would like this to be seperated into our monthly, quarterly, semi-annual and annual donors.
The challenge is knowing how to search through a column to find the, for example, "Monthly" entries and once these have been found to find the quantity of the donation(s) in the rows with "Monthly" in them, and then, to determine the number of months they have been paying for during the financial year (their first payment dates are listed as 01/MM/YY) - i.e, the difference between the end of the financial year (03/07) and the start (04/06) or between the time they joined during the financial year and the end. Then to multiply the number of months the direct debit has been active during the year by the amount they donate.
I've been writing a piece of VBA code to process a timesheet for my office.
THe sub routine has a number of variables passed to it, two of which are called CFhrs and Limit. The first contains in integer representing a number of hours. THe latter is a number of hours that can be claimed. In short if CFhrs is less than Limit, the CFhrs should be left alone, but if CFhrs is more than limit then CFhrs should then equal Limit.
First I tried this:
If CFhrs = Limit Or CFhrs > Limit Then CFhrs = Limit CFmins = 0 End If
This seemed to be totally ignored when run, and even when CFhrs was greater than Limit, CFhrs was left just the same. So I tried:
I have a table(see attached). I want to sum the total hours worked in Col C. Col B has the number of people on the job (either 1 or 2). I need a condition, I guess , so that if Col b has a number 2 in it, then the adjacent cell needs to be multiplied by 2, then summed into the total hours worked.
I am not sure on how i would write a formula for this. Looking at the sample if I add the total hours from col C at the moment I get a total of 18 Hours but as cell C3 & C5 have a "2" in their adjacent column, the total hours answer should be 27 hours.
