I think this may be impossible but what I am hoping to do is in B3 Return the date that is 3 working days before the date in B2. Working days being Monday to Friday in this case. so if the date in B2 is the 16/04/2014 3 days before this is the 13/04/2014 which is a Saturday so i would want the formula to return the 12/04/2014 which is the Friday. Is this possible?
View 2 Repliesi have an excel doc. with three sheets...
i may start that macro on any sheet any cell
the 1st thing that macro does is to go to sheet1 and after running the whole code it calls another macro... macro2
now macro2 runs in an infinte loop and runs evry 20 sec. the problem is every 20 sec the cursor comes back to sheet3.. i want the macro to return to the place where the user was working.. best wud be that the user can work seem lessly...
I have a formula to find if the Symbol "!" is contained in the a cell. And i want it to input "Undercut Due to Trim Edge" if there is a "!" symbol and blank if there is not. What did I do wrong?
=IF(ISNUMBER(SEARCH("~!",AL57:AV57)),"UNDERCUT DUE TO TRIM Edge","")
how do i get the previous days working date in VBA, how can i get VBA to determine wether we are in the week, and how will i get it to know its a monday to retrieve fridays date?
View 3 Replies View RelatedI am trying to write a sub for using vlookup. To enable the vlookup, I want to return a word in a cell to the cell in the next column. I use the InStr function, but it doesn't work. They all return as "Other".
Attached is the worksheet and here are the codes : Capture.JPG
[Code] .....
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
I was looking for a final result as follows
21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)
21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)
I am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.
I also need to be able to:
Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)
I have attached my sheet : 2014 Gatwick Quote Log (Macro Enabled).xlsm‎
How to return address of the column or cell I selected from Application.Inputbox, not just return the value?
[Code] ....
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies View RelatedI am trying to find the largest value, second largest, and third largest value for a column of numbers then return a content value for the adjacent column that contains the name for which each. The problem I am running into is when the second highest has the same value as the highest and also when the third value is zero (n=meaning there are only non-zero numbers for the column). I am using these formulas currently whic work when the numbers are not the same and I have atleast 3 non-zero numbers but I need to modify in order to account for the problem stated above...
View 8 Replies View RelatedHi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies View RelatedI am using the averageIf function and I need the cell to show 0 when then results are not positive. How to resolve my calculation
=AverageIf(a1:a10,">0",a1:a10) displays #DIV/0!
I want it to show 0 only
I created a spreadsheet with many CF's and save the sheet on our network, however, when my manager tries opening it the CF's do not work. they are there when I click on CF > Manage Rules, they just not working, neither is the Data Validation Drop Downs?
View 7 Replies View RelatedIn attached file I need doing sum where total working hours are not showing correct.
TotalingTime.xlsx‎
In A column the date something is received will be entered.
In B column there is a drop down list that has "insured" "Fee" "Aged Debt" "Other".
In C column I am trying to get another date to populate dependant on what is picked from column B, so if Insured is picked then 1 working day needs to be added to the date in column A and populate in column C. If any of the other 3 options are selected 5 working days needs to be added to the date in column A and populated in column C.
I have been using this formula: =IF(B2="","",IF(B2="Insured",A2+1,A2+5)) however this is not working days and I am stuck how to get to add working days
I have a dropdown answer column (J) to select an answer and in column (K) next to it that scores the answer. I'm trying to sum the score of (column K) in cell K7 but can't seem to get it to work.
View 3 Replies View RelatedI defined few name in manager but that are not working. I Defined names with multiple sheets with same Column.
See the attachment : Demo.xlsx
I'm using Excel 2007.
Is it possible to know which row code is active on whilst it is running?
s/s is 332324 rows and growing. When I run code which goes down one column and takes quite some time I don't know what stage it is at.
I'd like to be able to see on screen which row it is down to.
I have a column with hours in the following format HH:MM:SS, that looks like this:
12:16:00
1:35:00
1:35:00
12:47:00
12:46:00
12:51:00
4:33:00
4:05:00
12:54:00
3:19:00
3:19:00
I am trying to add the sum total of all hours, but its not working. I have my column formatted as time -> 37:30:55
=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),('Data Sheet'!E$2:E$997*IF('Data Sheet'!E1<=$F$4:$G$4,1,0))
The last part of the formula is the problem, the first two arrays are conditions and the last array is a sum if f4 a period of the year is equal or less to the data sheet tab it is summing.The formula below works fine, but when the last condition is added it doesn't work.
=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),'Data Sheet'!E$2:E$997)
I am using and index match formula that looks at names and dates. The dates are fine. The names are fomatted the same in both lists. Two names identical in each respect give me a #N/A result. It should match but it does not. How do make each formatted the same. Very strange......
Example:
Chuck
Chuck.
Cells H5:S5 have a formula in it to determine whether or not the allocation calculation needs to be made. I figured it to be straight forward enough
View 3 Replies View RelatedThe following is a simplified example of my problem: I have a list of product item numbers in column A. Next to each item number in column B, is a hyperlink to a PDF file relevant to each item. Elsewhere in the sheet, is a drop down box containing the item numbers. Next to the drop down box is a HYPERLINK(VLOOKUP) that fetches the relevant hyperlink depending upon the selected item in the drop down box. Problem is, when I select the ‘lookup’ link, I get an error message ‘cannot open the specified file’ (the original Hyperlink works fine). Does anyone have a solution to this problem (preferably without resorting to Visual Basic?
View 13 Replies View RelatedSometimes when I right click on a worksheet (filled with formulas, formatting, etc)and select move or copy and then click on "create a copy" a new BLANK tab is created rather than a new worksheet with my data. I'm not sure why this happens sometimes. This usually happens from a spreadsheet I downloaded from a website that I work on.
My workaround is just to copy and past the whole sheet to the newly created tab, but it's frustrating that it doesn't work the normal way.
Got this neat Calendar from another thread on this site,but it has problems. Played around with what little skills I have with no luck. When you open the calendar go to Jan, 1992 click on first four days and everything works. When you click on Thu 2 the problem begins.
View 14 Replies View RelatedI notice that sometimes the "Go To" function does not work. When I click on the named range Excel just brings the cursor to a the first cell of a worksheet rather than the actual cell/range. I find that I need to restart Windows in order to fix this. A simple restart of Excel does not work.
View 4 Replies View RelatedI can not figure out why only the color index 5 and 8 are working in the example- (1). To tun it say "no" to the initial prompt that comes up and select 1 from the dropdown in A2. Before some additional functionality was added it was called TestBook and that highlighted appropriately.
View 2 Replies View RelatedIs there a way in excel to say if i select Item1 and it is 100mm x 30mm x 100mm, excel will say this will fit into a compartment called A1 and may also fit into a compartment called B6.
I have a list of all the compartments and what the internal dimensions are. So is it just a matter of saying if these dimensions are within those of the compartment then that item will fit in.
I have a list of some what 6000 Items that have dimensions. I may pick 200 or so of these items to fit into special compartments of a machine. Because of the varying sizes i need to know what compartment each item will fit in so i can build a machine big enough and get the right size compartments.