I have the following formula in a cell
=LOOKUP(WEEKDAY(A1),D2:D8,C2:C8)&A1
that I want to look up the Day (mon, tue, etc) and then return the date entered in cell A1
So if the date in cell A1 is 01/05/08, the formula should return Thu01/05/08.
Currently it returns Thu39569, even though the cell is formatted as a date format... How do I get it to return the date in a date format?
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
I have a column which has either EDC or EDT in it, can I add a function to this column which says 'if EDC then output Eau De Toilette' and then Eau De Toilette get's ouputted into a seperate column? Is this kind of thing even possible in Excel?
View 14 Replies View RelatedI have a bit of code someone on this board provided and I want to make it work for two different ranges. If I just paste it, I get an ambiguous name error. How do I make it work for a second range?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = [A2:A101]
If Intersect(Target, rng) Is Nothing Then Exit Sub
If IsEmpty(Target) Then
Target.Offset(0, 4).Value = ""
Target.Offset(0, 1).Value = ""
Else
Target.Offset(0, 4).Value = Application.UserName
Target.Offset(0, 1).Value = Date
End If
End Sub
I want to add two times together.
14:00 (time) + 03:42 (duration) = 17:42
Is there vba code to be able to do this or an excel function?
I have tried searching but strangely couldnt find anything?
Below is my function.
="Profit $"&SUM(IF(Sold!D15:D8998<>"",IF(MONTH(Sold!D15:D8998)=A4,Sold!H15:H8998)))
I would like to edit this function to also count profit only if Cell A15:A8998 in my Sold sheet has the words "Shipped". The text value Shipped is not a text value I entered, it is automatically written based on a Vlookup formula I created.
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480)*(MONTH(Sold!$M$15:$M$8998)=12)),"")
I would like to edit the function above and add a third criteria. If Sold!S:S,"Returns" to my sumproduct.
I tried to edit it myself with the function below but it didn't work.
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480,Sold!S:S,"Returns")*(MONTH(Sold!$M$15:$M$8998)=12)),"")
I have the following IF function in a spreadsheet. =IF(AE2 = "AZ", "We've recently changed our name from Company A to Company B.", "")
How do you Make it so there is a Space at the end of the period?
I am trying to rank a list of numbers, such as:
1
3
5
3
4
1
I have no problem with the rank function in terms of the ties showing as duplicate values, however, when this occurs I would like a "T-" to appear before ranks that are tied, and show nothing if they are not tied. Essentially, I want the final result to look like this, without having to manually add the "T-" after the ranking is complete.
1 T-5 3 T-3 5 1 3 T-3 4 2 1 T-5
I have done more complex Excel formulas before, but for some reason this is stumping me.
If I have a sum function that adds up:
= SUM(U41:U45)
but I add a row at 41 I now get sum function
=SUM(S42:S46) when I really want it to incorporate the just add row to look like this:
=SUM(U41:U46)
I have the below code of which I would like to addresses added to the CC list Also, Would it possible to add a range as the body? E.G:
.CCaddress = "somebody@mail.com"
.CCaddress ="somebody.else@mail.com"
Body text = Range("A1"& "" &"B1")
Code that need the above inputted:
With ActiveWorkbook
.SendMail Recipients:=Array("steve.howard@kuehne-nagel.com"), _
Subject:="610 Cambridge " & Format(Date, "yyyymmdd") & " " & "Counts"
[add cc address + body]
.Close SaveChanges:=False
End With
I'd like to do is click the delete button and when clicked, it will search for matching records in column A & B and if they match... I'm thinking the code for that is <> but I'm not sure, then delete that record, and shift the cells up. Do this until the search results are empty below the delete button. Like I said, it's probably more understandable to look at the workbook.
View 5 Replies View RelatedI am currently tracking online PPC keyword reports with Excel and need to know if there is a function that will find and match words and phrases and then add the columns that are queried for the matching words/phrases. I think an example is definately in order.
December PPC
Keyword Clicks Cost Revenue
large dogs 45 .18 $12.00
small dogs 35 .25 $15.00
January PPC
Keyword Clicks Cost Revenue
large dogs 12 .14 $8.25
small dogs 18 .18 $5.35
Now using the example above I need a function or maybe even a macro that will scan all "keywords" and find a match for each keyword each month, say large dogs, and then add the clicks, costs, and revenue columns and post them on a particular row or rows.
So when running the function it will find and match "large dogs" for each month, it will then add the clicks for all months with "large dogs" in it and then populate a column or row with the total along with the keyword "large dogs" next to it. Is there a function that will do this or maybe even a macro?
Hi, looking for help desperately in fine tuning a formula. I have a formula at the moment (which works) for searching through a list on a separate file and totalling up all values which relate to it, see below:
=sumif([filename.xls]1’!$B:$B,D10,’[filename.xls]1’!$H:$H)
The tab ‘1’ in the formula relates to the first of the month so this month there are 28 different tabs with similar information.
With C10 containing the date in this instance, does anybody know a way of making ‘1’ a variable so that entering ‘04/02/10’ would change it automatically into a 4? (Unfortunately for me changing the 1 to =c10 didn’t work).
I am looking to be able to alter my table_array section in VLOOKUP to adjust in date.
exampe: =vlookup(A4,'[Daily report - August 25.x;sx]Facilities'!A4:AY100,84,FALSE)
and I want to be able to change the August 25 -> August 26 repeating so that as I drop the next date in it will update to the correct tab.
I have the dates above so if I could somehow just the date to another cell instead that would work as well. I just do not know how.
Essentially I need to grab data from a separate workbook everyday and compile it to one master list.
I am using Excel 2003
I have used =IF(I6=J6,1,0) but I want a 0 value if the two cells are blank. How do add this to the formula?
I have attached a copy spread sheet. This has been working great but i have been asked to add some items and i dont want to screw up the working functions.
I now want to add incert two columns so the actual costs of a first and second service can be added to the contracts and used contracts sheets, this information allong with data from a,b,e,g h needs to be copied over into a new sheet (report sheet) which will have the budgeeds costs in column i,j starting from row 3 and finding the last row so as not to overtype so that a report can be sent showing profit/loss.
The costs will be put in at diferent times so it only need to up date a changed cell
If i just add columns will this effect the auto archive coding? Could the data be copied over to the new sheet using the original code on start up? (so customer etc copied then as cost are put in these would be added to the respective rows on each start up.
I have had to remove some of the sheets to up load this so my not work correctly, but you can see the funtion in the code
I have a spreadsheet with 27 Columns and 439 rows of data. I need to copy each row of data that has a certain criteria and paste the same data 141 times below it and then manipulate the data. In the same spreadsheet I need to copy a row of data that has other criteria in it and past it 30 times below it, and then manipulate the data.
I have been using the copy and insert copied cells function, but I have to scroll down 141 or 30 rows each time to ensure I add in the correct amount of rows. Is there a more productive way to do this? I have about 10 workbooks with approximately 47 tabs/worksheets each that I will need to update in a similar fashion.
I have a piece of code that hides unneeded rows, it does work but it is very slow.
This is in the worksheet part of the project. My problem is as it runs it "jumps" in to a function I have in a module that counts continuous rows. I would like to know why it is doing this and what I need to do to stop it? I have another piece of code that is structered exactly the same that hides unneeded columns and I do not have any problems with that. I know both pieces of code are dealing with rows and I think this is part of the problem but I can not see any reason why the first bit would call the second bit?
Dim C
With Worksheets("CEN OAS"). Range("D5:D378")
.EntireRow.Hidden = False
End With
For Each C In Worksheets("CEN OAS").Range("D5:D378")
If C.Value = "" Then
C.EntireRow.Hidden = True
End If
Next C
Function to count used rows....................
Is there a wat to have the date refresh itself in a cell when you open the spreadsheet?
View 3 Replies View RelatedTrying to automate the period part of the impt function
To calculate current value of loan i have the below formula below with the 3 being the current period
=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)
What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.
I have this sample excel ,and I would like to add each value on the 6th row under the date .But I would like to add values under the same date .
View 6 Replies View RelatedI want to put a date in one cell then in the next cell i want it to add 60 days automatically, so to give the new date.
View 4 Replies View RelatedField 1 shows as MON 02/06/08 on sheet 1
I would like it so that Field 1 shows TUES 03/06/08 on sheet 2 and so on for every sheet for the remainder of the month.
I would also like it if I could just edit the first sheet then hit print 31 copies and have it count up each for each sheet that is printed.
Or another alternative is to change sheet 1 and have every sheet after that change in succession and then just print the entire workbook.
I have a date on the top left hand corner of my excel sheet. I want to add exactly one month to that date in a vertical list (without using micros)
Example:
1/4/2005
1/5/2005
1/6/2005
1/7/2005
1/8/2005
.
.
etc
PS: I tried adding 30 (or a similar number like 29). It works but not if I want a large list of dates, which is what I want.
help.jpg
I'm new to VB. I am writing a code to insert a value in to the textbox depending on the focus.
For example if i have kept the focus on textbox1 and I click the particular date on calendar, then it should print the date on textbox1 and vice versa.
i have a macro that richard buttrey has so kindly helped with
View 14 Replies View RelatedBasically it's to make my content management easier.
I have made a button and assigned a macro to it. At the moment the button creates a new column and makes it the right size etc.
From an outside source, a date is brought into one cell, and time is brought into another. Is there a way to combine them into one cell with the format of m/d/yyyy h:mm?
I tried concatenate, but that did not work.
I have a worksheet where in column B I have a vendor name, column E I have a date value and in column AF I need to calculate a due date based on 7 working days for vendor A and 14 days (not working days) for vendor B. I've tried to write an IF statement but can't do it. Can anyone suggest the right combination of IF/AND statements that would do this? I attach the workbook should my useless attempt at explaining my problem be unclear...
View 3 Replies View Related
