Excel 2010 :: VBA Timestamp When Value Changes By Formula?
May 7, 2014
I've been looking all over for the most basic of VBA codes to insert a timestamp in a single cell (B1) when cell A1 changes due to formula result change. All the answers I've found are for manual updates of A1.
A1 has the simple formula: =SUM(F1:F10000)/3. I would like cell B1 to insert a new timestamp when the results of this formula in A1 change. On a weekl basis, I will paste-value data into the whole F column, which will change the resultes in A1.
If this can't be done, or is too complicated (I don't really write VBA, only copy and paste basic code), is it possible to have a timestamp inserted into B1 based on the paste-value event into the F column?
Excel 2010
View 2 Replies
ADVERTISEMENT
Jul 3, 2014
I received some time stamped data in this format: 06.08.06.000000000 PM.
convert to HH:MM:SS ?
View 7 Replies
View Related
Jul 24, 2014
I have a query that returns timestamps that looks likes these:
53425,114500,182834.
How can I display these numbers as AM/PM format?
View 4 Replies
View Related
Feb 20, 2014
I am using Excel 2013 on a windows 8 machine. It may be easier for me to explain the problem, before posting about the formulas. I maintain a retirement residence with alot of phone numbers. I have a list of all the numbers we own in one column and the numbers assigned to the PBX systems in a second column. By using a If formula I can tell which numbers have not been assigned (The true statement). My problem is when a resident passes away, I need to reuse that "phone number", but I dont want to reuse the number too soon. I am hoping to use the "true" from the if statement to generate a time stamp. I plan on using that time stamp against the "today()" in a days formula, I believe this will spit out the number of days a number has been idle. The time stamp would be constantly changing or removed as the if statement would always be changing. So column C gives the True statement and says "Number not Assigned" - this should create a time stamp in Column W
A
B
C
[Code].....
View 4 Replies
View Related
Apr 9, 2013
I have the below code that works fine. However I want to use a countif function to update Column I instead of manually having to type in the numbers each time they change. I know it would probably be a Worksheet_Calculate Sub but cannot seem to find one that works. The time stamp would be put into column H and the first row of data is row 3.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("I3:I12"), .Cells) Is Nothing Then
Application.EnableEvents = False
[Code] .....
View 2 Replies
View Related
Dec 16, 2013
I have a tab with about 10,000 records with IDs and timestamps (date and time). On another tab I have a list of IDs and start and end timestamps. There are duplicate IDs on both tabs. I want a formula for the 10,000 records tab that checks the ID and timestamp for each row against all the IDs and start/end time stamps of the other column and flags the rows where the ID and timestamp fall within the start/end timestamp on the other tab. It seems pretty simple, and it feels like the code should be something like this (but this code doesn't work):
=IF(AND(F2=List!C:C,B2>=List!F:F,B2<=List!G:G),"Y","N")
where F2 is the ID and B2 is the timestamp on the records tab. The "List" tab has the ID in column C and the start/end timestamps in columns F and G respectively.
View 1 Replies
View Related
Mar 14, 2012
I am using Excel 2010 .I have set up Data validation for a dropdown box so I can select from a list of items. In the old versions of Excel the actual drop down arrow used to appear in each cell. In the version I have, the drop down arrow only appears when you select the actual cell. When I did the validation I checked the " In-Cell Dropdown", but it still doesnt put the arrow in the cell. Is this functionality available in Excel 2010 ?
My second issue is a formula.
The last name is in a list of items and users have to select Yes or No to theitems on the list. I am wanting to create another spreadsheet that automatically populates based on their responses.
In short, I want to be able to set up a rule or formula that states if the answer in column A is "y" then I need the information in column B to be displayed.
The ultimate aim is to get a automatic sub set, (in another tab), of the orginal information based on users responses.
View 2 Replies
View Related
Aug 11, 2013
I have Excel 2010. I am creating a spreadsheet for server backups. I have always been told to take the current size of the disk and multiply it by 10% to figure out the backup size needed for the backup. I am trying to create a formula to do this. I have a server with 25GB. The formula i tried was
=a1*10%. A1 being 25gb. Excel does not like the GB in the cell also this is only giving me the difference. I need to know what size to allow for my backup on this server.
View 9 Replies
View Related
May 21, 2014
I'm trying to create a formula that states. If tank 1 maximum capacity is less than 500,000 and the difference in inventory is greater than the absolute value of 10% or 20,000 or if the tanks maximum capacity is between 500,000 and 1,000,000 and the difference in inventory is greater than the absolute value of 20% or 100,000 or if the maximum tank capacity is greater than 1,000,000 or the difference in inventory is greater than the absolute value of 30% or 300,000 then Y OR N.
I figured out the absolute value portion of the formula but I'm not really sure how to combine that with the tank maximum capacity piece.
=IF(OR(ABS(T14)>10%,S14>20000),"Y","N")
View 7 Replies
View Related
Apr 4, 2014
I am using Excel 2010. I am extracting text and data info from a single cell and inserting the info into different cells. Let's say the following formula is entered into cell M28:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), "")
This tests cell J28 to see if the text string "IM" is present in that cell.
If it is, then the text "IM" plus the following eight characters become the result in cell M28.
If the text string "IM" is not present in cell J28, I want the contents of cell M28, that is the formula itself, to be deleted from cell M28. EG below:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), function here to delete this formula from this cell)
( logical test ) ( value if true ) ( value if false )
View 4 Replies
View Related
Aug 14, 2014
I have attached an excel 2010 spreadsheet to show the issue.
I was shown a formula to rank a series of numbers =RANK(A2,$A$2:$A$19775,0).
However, the ranking figure that get's generated, appears to be counting the cells or something like that.
Ranking Example.xlsx
View 10 Replies
View Related
Mar 14, 2014
I would like to create a function where I would be able to ask and answer 20 questions. When using If statement it only allows me to use up to 7 nests.
I have created an example below with all the statements I need using IF but of course this doesn't work so I would need so how to handle this particular issue with so many questions/answers in one. Even though the delta between 5-4 and 3-2 is same I want different answers. I'm using Excel 2010.
=IF(AND(AK58=5;AL58=5);"OK";IF(AND(AK58=5;AL58=4);"1 OP";IF(AND(AK58=5;AL58=3);"2 OP";
IF(AND(AK58=5;AL58=2);"2 OP+1 NV";IF(AND(AK58=5;AL58=1);"2 OP+2 NV";IF(AND(AK58=5;AL58=0);"2 OP+2 NV+INST";
IF(AND(AK58=4;AL58=4);"OK";IF(AND(AK58=4;AL58=3);"1 OP";IF(AND(AK58=4;AL58=2);"1 OP+1
[Code]...
View 7 Replies
View Related
Dec 19, 2011
When I open Excel 2010, the formula bar appears about 6 times as tall as normal. I have not been able to find any way to reset it to normal size.
View 11 Replies
View Related
Jun 4, 2014
Excel 2010. When the result of the formula in Col. E is less than zero, I need to move the row to another sheet, and then delete the row.
I modified another poster's script as follows:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Move rows with negative value in E to Flagged Items sheet
Dim rng As Range
' Set Target Range
Set rng = Target.Parent.Range("E2:E200")
[Code] ........
The script works perfectly if a negative value is manually entered into a cell in Col. E, but not when the result of the formula produces a negative number.
I can't find a way to make the Case Is < 0 act on the resultant value rather than the formula.
Is this possible in the script or is there another way to achieve the desired result?
View 4 Replies
View Related
Jan 2, 2013
What will show in column B will change each day as a I am creating a ordering picking list and want to show a pic in column A of the item to pick the item. Is there an easy way of doing this.
I have been messing around with the following
=showpic("C:DesktopItems46004978Thumb.jpg")
And thought I may be able to show this as by doing the following in the desired cells
=IF(ISBLANK(B1),"",INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,0)))
On sheet1 I have all the model numbers next to
=showpic("C:DesktopItems46004978Thumb.jpg")
And the index formula will grab the correct showpic string depending on stock code that is showing in various column B cells but this isn't working.....
View 9 Replies
View Related
Jan 29, 2013
Excel 2010 and the cells are formatted to time
Cell G1 = >8:45 want Cell H1 to deduct 45:00 minutes
Example Cell G1 = 10:00 want Cell H1 to show 9.15
Example Cell G1 = 9:00 want Cell H1 to show 8:15
Example Cell G1 = 8:00 want Cell H1 to show 8:00 no deduction
Example Cell G1 = 6:00 want Cell H1 to show 8:00 no deduction
Example Cell G1 = 0 want Cell H1 to show 0
View 9 Replies
View Related
Jun 10, 2013
I have a workbook with multiple tabs that I update every other month with current data. I have one tab for data that I clear each time and copy the updated info into. (That info is copied from another excel spreadsheet, by the way.) On the second tab I have sumifs formulas set up to pull in certain data from the data tab. This setup has worked perfectly until today. I copied my data into the one tab but could not get the formulas to calculate. It looks like the formulas aren't picking up the data. I've checked to make sure the formulas are calculating automatically. I've retyped the formula. I've replaced the =. I tried naming the ranges I wanted the data to be picked up from. I've tried changing the format of the data - general, text, numbers, etc. I'm working in excel 2010
View 2 Replies
View Related
Nov 25, 2013
I have Excel 2010 & Windows 7
I am using the following formula to copy the name of the tab - =MID(CELL("filename",C26),FIND("]",CELL("filename",A1))+1,6)
I have a marco that creates a tab from a Master file, and I need to copy the name of the tab into cell A2, using the formula above. Then I use a vlookup on this cell, to get the data needed for about 10 other fields. I have these vlookups in the Master file. The vlookup doesn't work all the time. I get a #NA answer. Sometimes it does work. When I manually type in the project number on the tab, the vlookups work.
Sample of the vlookup: =VLOOKUP(A2,'AC-Program'!$A$1:$H$58600,2,0)
I was trying to find a way to use a value in cell A2 instead of the formula above, but I don't want to have to manually update it, I need it to be generated when the Marco generates the tabs.
Here is my macro:
Sub CreateSheetsFromAList()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")[code]......
Is there a way to add this function into this macro, and it will leave this field as a value instead of a formula?
View 2 Replies
View Related
Jun 25, 2014
In excel 2010, I have 2 spreadsheets, the first "Active Total Master Turn" has the data, the second, "Pending" has a reporting chart. A cell on "Pending" contains the following formula:
=COUNTIFS('Active Total Master Turn'!F:F,"RK*",'Active Total Master Turn'!H:H,"Painting",'Active Total Master Turn'!C:C,"
View 7 Replies
View Related
Apr 29, 2014
I am looking for a function or a formula that performs the same process in Excel 2010 as:
Data Tab -> Sort & Filter -> Advanced -> *radio button* Copy to another location -> List range: A1:A100 -> Copy to: B:B -> *radio button* Unique Records only.
View 2 Replies
View Related
Apr 30, 2014
Two formulas which I need in Cells E74 and E75.
Basically the formulas will depend whether the entry in D73 are Yes or No
in cell E74, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF ‘D73’ = ‘Yes’ then E74 = E70
in cell E75, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF D73 = ‘Yes’ then E74 = E70
Excel version 2010
View 4 Replies
View Related
Jun 20, 2014
How do I combine a formula and text in the same cell in excel 2010.
What I am looking for is the sum of a number of cells, followed by text.
e.g. something like =SUM(E4:E50)"/35"
If the sum result of cells E4:E50 was 10, I would be expecting to see visible in the cell '10/35'
How do I write this formula?
View 3 Replies
View Related
Aug 7, 2014
(Excel 2010). I have 3 cell that contain data. What I need is to compare the 3 cell and return the data that has different value into 1 new cell.
I have attach an example : test.xlsx
View 3 Replies
View Related
Feb 18, 2014
I'd like to apply multiple formula to a set of cells on a summary page. My summary page also contains 3 variable dropdowns, and I'd like to display data based on the text selected in those dropdowns (pulling data from 2nd tab "Variables")
The following formula works in the first instance:
=IF(AND(H4="Product Type A1", H6="External", H8="Existing"), Variables!C4, 0)
What I'm struggling to do is add additional formula to the same cell in order to deal with the remaining eventualities of the drop down variables:
Variable 1:
Product Type A1
Product Type A2
Product Type B1
Product Type B2
Variable 2:
Internal
External
Variable 3:
Existing
New
Or am I better using a VLOOKUP or something?
View 2 Replies
View Related
Jul 4, 2014
I have attached an example workbook where I have three worksheets i.e. Data1, Data2 and Master Data all of which have the same data format in each column
I have data in the Data1, Data2 tabs and was wondering if it is possible to copy the data from both these tabs (using formulas) onto the Master Data bearing in mind the rows of data in the Data1, Data2 can vary on week to week basis.
Excel version 2010
Attached File : Data.xlsx
View 2 Replies
View Related
May 16, 2014
I am trying to use the IF formula or a similar VBA/Macro to color certain cells. We deal with 16 different sand types that come in on rail cars. I want to put in the sand type in a cell; EX: C11 has text "30/50BH" if this statement is true to color cells A11-D11 yellow. If it is not a true statement to check for the next sand type, "20/40BH" and go on from there. If I can get an example of what to do I can build it for the 16 sands we have.
The formula I have in mind would be something like
=IF("30/50BH",[colorA11:D11,Yellow],[IF("20/40BH",[colorA11:D11, Purple], .......
I'm sure there is a less brute force method of doing this, but my knowledge of programming and excel is limited. I am using the 2010 version of Excel.
View 14 Replies
View Related
Oct 6, 2011
I'm trying to create a macro to insert a formula into a specific cell. The formula is meant to check if a cell has text, and then if it does, search for the text on another page.
I had a go at the code, but keep getting Runtime error 13.
I'm using Excel 2010
Code:
Sub new_entry()
'
' NEW_ENTRY Macro
Dim rowNo As Integer
[Code]....
View 6 Replies
View Related
Aug 7, 2012
The formula I found is close but I need it to count, not sum.
=SUMIFS($C$36:$C$51,$B$36:$B$51,$B36)
Example data: [972 1] [972 2] [972 3] [214 1] [214 1] [765 1] [765 2]
I want the formula to count the different numbers in the second column based on the first column. The results should show, 972 equals 3, 214 equals 1, 765 equals 2.
I am trying to separate this out but it keeps putting this paragraph.
View 3 Replies
View Related
Oct 10, 2012
Im trying to enter a formula for volatility in my excel 2010 spread sheet
my hig low close is listed verticaly in A,B,C
The formula for D1 is supposedly “={LN((A1)/(B1))}^2”
The formula for E1 is supposedly =SQRT(SUM(D1:D10)/10)*SQRT(252)*100
I put this in D1 and it is giving me an error
View 3 Replies
View Related
Oct 11, 2012
USING EXCEL 2010
I'm looking to create a conditional format which highlights a date RED if it is within 90 days of today's date (the date the file is opened). What would the formula for that look like?
View 2 Replies
View Related
