I have a problem similar to Ozone64's posting of 4/23 that I am trying to generate automatic time/datestamps in a cell whenever a value is entered in an adjacent cell. I am currently using the function =IF(A3"",NOW()) but the problem is when data is entered in subsequent rows the previous timestamp is overridden with the new timestamp. Thus at the conclusion of data entry every row ends up with the same date/timestamp as the final entry.
I am using a linear interpolation UDF that recalculates every time a change is made to any cell in the workbook, whether that cell is pertinent to the calculation or not. Is this correct behavior?
View 9 Replies View Relatedwhy Microsoft have not made the indirect function non-volatile. In 1997, they changed the index function to non-volatile.
View 9 Replies View RelatedI have searched around the forum but I could not find anything related. Like the title says, I need to have a cell that is the current day of the month and changes automatically each day.
View 2 Replies View RelatedWhat can I use to replace the portion in red because it is volatile?
=IF(A3="N","",RANK(B3,$B$2:INDIRECT("$B$17"),0)+COUNTIF($B$2:B3,B3)-1)
I have a worksheet that will be referencing data from a 2nd open workbook.
Although the 2nd workbook will contain common worksheet names (eg stats), the file name will be volatile.
Consider the formula in wb ws1 cell A1 used to copy a value from wb2 (39961(28-May-09).xls) ws1 (stats) cell B1 if workbook 2's name was static.
='[39961(28-May-09).xls]Stats'!$B$1
In my case, the reference file will always be different and must be substituted in the forumla above (red portion). How can I adapt my formulae recognize the available workbook. I have the file name available now in cell C4 of WB1.
I am looking for a non volatile alternative to using OFFSET in my named ranges as my models are generally quite bulky and it is beginning to make me quite angry having to build these models in calc manual.
Can anybody provide a non-volatile alternative to this?
=OFFSET(Query!$A$14,,,Height-13,19)
With Height being:
=MATCH(BigText,Query!$B:$B)
Okay so I'm trying to us the SUMIFS formula to add values on a different sheet based on criteria on that sheet but I want to use a text string on the sheet with the SUMIFS formula to reference the sheet with the data on it.
My original formula was:
=SUMIFS(INDIRECT("'"&A2&"'!K4:K500"),INDIRECT("'"&$A$2&"'!B4:B500"),"=LEFT(AA1,2)",LEFT(INDIRECT("'"&$A$2&"'!C4:C500"),3),"=MID(AA1,7,3)")
However I kept getting an error and excel said that INDIRECT("'"&A2&"'!K4:K500") was volatile, so I tried to replace it with INDEX and my formula was:
=SUMIFS(INDEX(A2:A2,1)&"'!K4:K500",INDEX(A2:A2,1)&"'!B4:B500",LEFT(AA1,2),LEFT(INDEX(A2:A2,1)&"'!C5:C500",3),MID(AA1,7,3))
However INDEX(A2:A2,1)&"'!K4:K500" returns the value "1251062 EP.EL+CB-A10'!K4:K500" which wont calculate properly because of the quotation marks but excel is auto inserting them.
I have a model that does a projection for 36 months ahead on a spreadsheet. The projection depends upon a randomising "volatile" function. When I press recalculation a new projection is generated. I want to capture the set of numbers that appears at month 36 through repeated recalculations written to a separate sheet. I want around 500 of these numbers.
I have no experience with macros so doing it with preset functions would be preferable. If it has to be a macro, the easier the better. If invoking the spreadsheet and outputting to a flat file is easiest that is fine too.
I've posted this question in another site http://en.allexperts.com/q/Excel-105...xExp_72253.htm , but i haven't gotten any answer. I'm attaching an example of the file to show an idea of what i'd like to do, please check first. As you can see, i have a Due Date and Priority columns. The Due Date column has a formula, that when i choose the Priority level, it calculates the Due Date from the current date and auto populates in the cell. The problem is, when i open the file the next day, and insert another Priority level, it updates everything even the previous Due Date entry. This is the formula i have in place for the Due Date:
=(TODAY())+SMALL(IF(WEEKDAY((TODAY())-1+ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0))))<6,ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0)))),(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0)))
Is there a way to make up a vb macro to be able to make the Due Date output static, without affecting the formula?
In the sheet I am working on I have some numbers that represent todays activity. These numbers are volatile and reset when the date advances. I have a column on another tab that has all the days of the month as F4:F33
The results I want will go in the G4:G33 column.
C2 is Today() to avoid too many Today()s.
Active!J10 is the value I need in the G column.
Basically what I need is the value from Active!J10 to be copied to whichever cell corresponds to the date next to it in the G4:G33 and then stay there when the date advances
The formula I am using for the g range is :
=IF(F24=C2,Active!J10)
=IF(F25=C2,Active!J10)
=IF(F26=C2,Active!J10)
And so on down the column.
I dont mind the FALSES for future dates, but the current one changes to false upon the advancement of the date. Basically, I need a running log of the final numbers going down the G range and for those values to stay there when the date advances.
I created a user defined function and using Application.Volatile to update sheet automatically.
But this slowing down my sheet execution alot.
When i remove that it doesnt take much time but no automatic update.
Is there a way to put a time stamp with the users name into an adjacent cell when a cell is updated?
In case that didn’t make sense I will tell you what I need this for. We are creating new time cards that require both employee and manager approval on them. We have many remote employees and getting their signature can be a pain. If they could click a button on the time card that stamped their name and time that would be ideal. They would then send to their manager they would click to verify the time and it would stamp it as well.
I would like to set up an Excel spreadsheet for conducting a time and motion study in a workplace setting where someone follows an employee around to document how much time is spent on a variety of activities. Since the person would have a mobile tablet PC to walk around with and track the data, I would like to capture 3 fields of information:
1) Category of function being performed
2) Start date/time of function
3) Stop date/time of function
For #1, I would like to have a drop down box that appears in each row of Column A with a predefined list (i.e. Activity A, Activity B, Activity C, etc.). For #2 and #3, I would like to create a macro that "timestamps" the current date/time when a blank cell is "clicked".
=IF(D2<>"",IF(A2="",NOW(),A2),"")
Whenever I type into field D2 it populates current date and time in field A2 and down the A column as I write into current D columns. The next step I would like to do in which he was able to figure out so far is how to lock in the date and time after something has been typed into D2. If I make any changes to the D2 field it updates the date and time, which I dont want to happen.
What formula would I use if I wanted this:
If A1 is greater than 1 then B2 equals the time that I entered a # in cell A1
Is there anyway to create a timestamp based of a cell linked to a Listbox? For example if the linked cell is in cell C8, cell D12 would stamp the time.
View 1 Replies View RelatedI am using the following code to add in an entry to the sheet named "Upload Log". I am currently using the "Now" function to input date and time. problem is everytime the sheet changes the formula changes and alters the times. I am trying to create a log of each time this code runs. so I have a record of it. This code seems to work but rather than placing the formula in the cell as I have written here, I just want the code to place the values that formula would place, but not the actual formula. How can I change this code to do that?
Code: ...
I have A column that has values 0 or 1 (product active or not).
I want to add into B column date when change occured (from 1 to 0) but simple NOW or DATE is not working becouse program works so that it erases/resets all values and calculetes tham again wich resets mine date also.
How can i remamber value 0 and if it stays the same after reset that date in corresponding cell in B column stays the same and not resets.
Maybe some form of check is there date in B before chacking status in A?
I am attempting to enter Timestamp data from a movie in this format:
minute:second.millisecond
When I enter a frame timestamp of 18 minutes, 42 seconds, and 5 milliseconds, excel auto formats this data to 12:18:43AM.
This is not what I want. I have attempted multiple types of cell formats in an attempt to enter this data without auto formatting. One requirement for the format is the ability to subtract two of the times for a change in time measurement. I have tried every setting under the "Time" format list, as well as custom formats, which fail to subtract.
I've seen a lot of timestamp solutions but none of them fit my needs. Also my VBE skills are not good enough to create this. So here's the problem:
I've got 3 columns E,F en H. H contains a validation list with 3 options OK, NOK and OPEN.
If H is NOK I would like E and F to be blank. If H is OPEN I would like E to contain a start date wich doesn't update. If H is OK I would like F to contain an end date wich doesn't update.
I am trying to set up my doc. so that when i double click with in columns (B&C)) the current time get inserted in a 24hr format. I am also trying to do the same thing but with a date formula (column A, also with the double click feature).
View 6 Replies View RelatedI'm trying to make a directory whenever the macro runs that includes a timestamp. File names cannot include "/" or special characters, so I'm trying to change the format. However, even though NumberFormat changes it, excel still recognizes the date as having mm/dd/yyyy etc. so the directory cannot be made.
Here is a part of the code:
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "yyyymmdd-hhmm"
DateTime = Selection.Value
strFileName = "Data Pull"
MkDir "C:..." & strFileName & DateTime
how I can modify the below code so that it populates the timestamp in the same row in Column E if Column A is populated with data...
Example:
Cell A1 is populated then populate Cell E1 with timestamp
if
Cell A4 is populated then populate Cell E4 with timestamp
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$A$2" Then
With Sheet1.Range("E2")
.Value = Time
End With
End If
End Sub
How to modify this code so that I select only one column triggers the time stamp update? For e.g. if i make any changes in column A, the date stamp is updated in the corresponding cell in column B. Basically, I am trying to narrow down to only one cell in the row, but it should work for any row in the sheet.
Refer to the below post: [URL] ....
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 Then Cells(Target.Row, "B") = Now()
End Sub
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] .....
I received some time stamped data in this format: 06.08.06.000000000 PM.
convert to HH:MM:SS ?
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.
I have a query that returns timestamps that looks likes these:
53425,114500,182834.
How can I display these numbers as AM/PM format?
I am creating a worksheet in excel 2003. Want to use this for attendance log.
As we input numercial data in column B, we would like in column I to create a formula that automatically inputs the actual time that info was entered in column B.