Is there a way for the current date to be displayed next to an item on my "Inventory" Sheet automatically once that particular item number is entered on the "Sales" Sheet, making sure that the date will not change/update (meaning TODAY and NOW are out)?
Also, this date would have to remain attached with that particular item number even if one or both of the sheets were sorted differently.
I have a user form that when a command button is clicked it enters the data from the from into coloums a,b,c,etc. I have code to do this but I want to add code to add a static date in coloum B based on if coloum A had data entered from the form. I need the date not to change to current date when the file is reopened. I am trying to elimate a date text box in the form. I have a link to the file http://www.box.net/shared/zdtsjv0qos
View 7 Replies View RelatedFilling in a webform using the code below, works fine until I add a bit to set a Value to a field name that comes from a Radio Button. I'm presuming its because it doesn't actually select the Radio Button (as seen when I step through, you can see all the other values/fields update). Updates the page with all the other values correctly updated except for changing the Park Status.
With WebBrowser1
.Navigate NMCURL
Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
With .Document.forms("CFForm_1")
.PageAction.Value = "Enter"
.SelectedCallID.Value = TicketNo
.lastactionby.Value = UserName
.ActionType.Value = ActionType
.Description.Value = Description...................
I have found threads that explain that if you want a to use the now() or today() function but make it static, you have to put a code in the worksheet relating to it.
I have read quiet a few threads, but i can't seem to make my code work.
I need to format column A to have the date format in it. I have a event procedure that puts Now() when run in Column A and I think i need to add the following code to the worksheet to make it static.
I have a rather simple question that I can't seem to find an answer to anywhere, even with a date stamp search on the board. I've found similar answers, but nothing specific to my problem.
I'm currently working on a department worksheet to track productivity. I have the formula in 2 cells to automatically display the current Sunday through Saturday dates when the worksheet is opened:=TODAY()-WEEKDAY(TODAY()-1) in E1 and=E1+6 in E2However, I need these dates to be static once the week is over for each worksheet and the workbook will contain a sheet for each week in the month (I'm not sure if that matters). I know this needs to be done in VBA, but I can't find how (if possible).
I'm new to this board and not a super Excel pro, so any help would be fantastic! Also, I'm working with the company's very old Excel97.
I'm pretty new to VBA and am working on trying to make a userform and I seemed to have gotten stuck.
I have userform with 6 textbox fields and 2 radio buttons. I need all the fields to including the radio buttons to be required. I currently have this set up.
Private Sub OptionSubmit_Click()
Dim nextrow As String
'find the next empty row
nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(nextrow, 1) = CsrName.Text
[Code] ........
The required fields still fill in data in the spreadsheet. I need it to check those fields and not allow the data to be entered into the spreadsheet.
I am attempting to create a formula which will detect of a cell is empty, and if it is empty enter the date by using TODAY(). I also dont want this date to change when opened after it has been entered. This is what I have been working with so far.
Code:
=IF(A1="",TODAY(),"")
Code:
=IF(G9>=TODAY(),A1=1,"")
I am trying to use the second formula to change the value in the cell "A1", but instead it just returns a true/false statement in the cell with this formula.
If I want date to be current and time constant how to do it for ex:
in A1 want display of 8/23/13 8:00 next day when I open excel A1 should display 8/24/13 8:00
so my date is dynamic but time is static.
Here is what I currently have to make the cell blank and highlighted yellow in the event the date in K1 equals the second day of any month
=IF(DAY(K$1)=2,"","N/A")
Conditional formatting is just =DAY(K$1)=2
What I need to do is apply these same conditions to the cell if the date in K1 is equal to 02/10/10 or 04/10/10 or 06/10/10 or 08/10/10 or 10/10/10 or 12/10/10
I am not sure whether this can happen, I want to capture time taken for different activities, without manually entering the details.I tried the formula "=Now()", but what happens is that at the end of the day the times are all the same because the live time is changing. Is there a way where the live time is captured and freezed, so that at the end of the day i have a time log sheet.
View 9 Replies View Relatedi am building a data base and every day, say, at 5pm a certain cell should contain a corresponding date. In my macro I simply wrote
Range("A1").Offset(i-1,0).Formula="=Today()"
I thought naiivly it would work, but it doesn't, as the next day the date will be changed correspondingly. So, the question is: how to record todays' date (Dec 29) so that this date stays in this cell forever? Only the date, not Excel Dates & Times
Here is the code I'm using
C5=IF(H5="X",NOW(),"")
C6=IF(H5="Y",NOW(),"")
C7=IF(H5="Z",NOW(),"")
Now H5 can be three different things, and all three different things are linked to three different cells. Once one cell gets populated with a date in this case is there a way to then have that cell stay populated even when H5 gets changed?
How can I add a date stamp that will populate a field when text is entered into another cell but will not change every time I re-open the sheet. I have been using =if(B4="",NOW()) which changes each time though and as soon as text is entered it disappears. I want to capture the actual date that someone enters text into a cell and save that date in another cell???
View 5 Replies View RelatedWhat I am wanting to do is have excel insert a static date automatically. I know ctrl + : inserts date and ctrl shft + ; inserts time. I want excel to do this automatically. The now() and today() functions auto update the date. I dont want the date auto updated when the file is reopened after it is saved. The file is a template so I guess what I am really wanting is a automatic date/time stamp when the user saves the file so when it is emailed and reopened the date does not auto update.
View 6 Replies View Relatedwhat type of vba code I could use to populate a static date and time that I could plug into a formula. Basically what I need is:
IF A1<>"" then L1 shows the date and time of the entry. If A1="" then L1 remains blank. I don't want the date to change once it has been entered.
I'm trying to create vehicle maintenance inspection program for a mechanic at work. The trouble is the he's not fond of computer and I'm trying to make it as simple as possible. There's a debug in the line
View 2 Replies View RelatedI am trying to automatically insert a time into B and a date into A every time something is entered into C. I want the time and date to not change from the time it was posted. What formula will accomplish this?
I tried using if(C"",now(),"") while having Workbook Calculation set to automatic, but it would update every cell in column B every time I entered data into a new cell in column C.
I have a spreadsheet that has the date in one cell. I want the date to remain static unless I make a change. ie, when I first create the sheet I want a static date to go into this cell. Then I only want this date to change if any changes are made to the workbook (several sheets), I dont want the date to change everytime I open the spreadsheet. It must however update if I make any changes to particular cells on various sheets.
View 9 Replies View RelatedI needed a static date stamp to be entered into cell H3 when a "1" was entered into cell E3. This is the formula I found, it works fine too.
Formula for cell H3.....IF(E3="","",IF(H3="",NOW(),H3))
However, what I need to do is static date stamp the same cell, H3 but when a "1" is entered into EITHER E3 or F3
REQUEST 1
What I need is lets say I have 3 columns Name, Date & Time
Name field has a drop down menu having a list of names
Now what I need is - lets say from the dropdown menu someone selects a name - THEN at the same instance the Time & Date fields gets populated with the Date & Time of that update.
and that Date & Time shouldn’t change if someone selects the same or different name in the row below
Kindly assist in lay man terms as I am a novice to Excel
REQUEST 2
In this format what I need is like above , the start date & start time should update real time ONLY when a name is selected
and when the End date is selected the End time , Date Elapsed & Time Elapsed should Populate automatically.
However there should be NO effect if data is entered or changed in the Request Id or Issue Cells - basically the triggers should only be Name field & End date Field
Can you attach an example sheet as well please
Have enclosed an sample attachment
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?
I belive i have a simple date stamp problem, but i do not know the correct VBA to edit the code posted below. I would like to have a datestamp placed in an adjacent cell based upon a particular value. For instance, If Cancel is entered into cell a2, then b2 would gave the date stamp. or if Started was entered into a2, then c2 would have the date stamp.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub
I recently found on this site the macro to auto insert a static date in one column when an entry was made in the previous column.
What I have in my spreadsheet and what I would like to be able to do is if I select a particular Order Status from a drop down list, that it auto inserts the date into the respective columns.
Column F contains the various Order Status indicators ie Order Submitted, PO Raised, Delivered, and Invoice Received.
The date columns that I would like populated and which correspond to those status indicators (in order) are Column G, Column I, Column L, and Column K.
So if I pick Order Submitted, the status date should be auto populated in Column G. If I pick PO Raised, the status date should be auto populated in Column I etc.
Any way where is a list of cells (b3:b113) has text entered, or specific text entered, it will enter a static cell in the corresponding "d" column.
I know of CTRL + ; but i don't really know how to get it to work as a macro, can't find a forumla that enters the static date, and i don't want to make a macro to copy + paste special the date when it happens.
So all i should have to do is entered text in column b, then it will add a time stamp, when that text was entered, it doesn't have to update.
Like all i need is this, but instead of the "now()" function, it will have another function that doesn't automatically update?
=IF(ISBLANK(E5)=FALSE,NOW(),"")
I have a column of values resulting from subtracting a static date and time from the current date and time.
This means it is constantly updating, which makes it impossible to sort.
All my work depends on sorting those values, though.
I am using the statement
Code:
Sheets("Sheet1").Range("A6").CopyFromRecordset rs
to populate columns A through to F. These records come from an Access database.
In column H through to L, content is being written for each record.
For example columns A to C are dynamic and H to I are static
A
B
C
H
I
1
Record 1
Some data 1
Some data 3
Manually entered data 1
Manually entered data 3
2
Record 2
Some data 2
Some data 4
Manually entered data 2
Manually entered data 4
The problem is when a new record is added to the database. It creates a new row for columns A to F however the data for H to L does not move. The same occurs when a record is deleted from the database.
A
B
C
H
I
1
Record 0
Some data 0
Some data 0
Manually entered data 1
Manually entered data 3
2
Record 1
Some data 1
Some data 1
Manually entered data 2
Manually entered data 4
3
Record 2
Some data 1
Some data 1
I have multiple VBA modules that all refer to directories on the local machine and across the network.
I am currently defining the directories in each module.
How can I define them once and then refer to them in any module?
Set Word_Template_Name = "Sanitary Survey Template.dotx"
Set Excel_Template_Name = "Sanitary Survey Blank.xlsm"
Set WQCDdir = "c:CO Sanitary Survey"
Set WQCD_Source_Dir_File = "...wqcProgramsSDWEngineeringTemplates"
This would ensure that any directory changes could be made once rather than 5 times.
I want to take some data which is dynamic and make it static elsewhere in a workbook without doing a manual copy of the said data. Is this even possible?
For example, let's say Sheet1!A1 is the dynamic data I am using, and on Sheet2!A1 I want to replicate this data, I know I can just reference Sheet1!A1. However, this would make Sheet2!A1 dynamic, and I want it to be static. I know I can do this be just typing it in manually but I have a lot of data I would need to do this for, is there some way of making Sheet2!A2 static whilst still referencing Sheet1!A1?
Objective: Copy filtered contents between two workbooks.
Reason: I spend HOURS copying and pasting data between workbooks. We get files on average twice a week that contain multiple tabs of data that must be broken down and sent out to the end-users.
Example: Monday filename=neverconstant.xlsx, 11 tabs of unique data, 110 users that need the data but business dictates user1 can only see user1 data so I must filter user1 in tabs 1-11, copy raw data, put into a new workbook while naming tabs same as neverconstant.xlsx.
Thursday filename=stillnotconstant.xlsx, 8 tabs of unique data, 110 users that need the data but business dictates user1 can only see user1 data so I must filter user1 in tabs 1-8, copy raw data, put into a new workbook while naming tabs same as stillnotconstant.xlsx.
My biggest hangup is the filename is never constant. I am trying to make something that would add a workbook, add and name necessary tabs, copy and paste data into respective tabs and prompt me to "save as" with the new workbook. I am still VERY new to vba so please dont laugh to hard when you see what I have so far.
Is it possible to have a dependent validation drop box that under certain conditions automatically produces an actual value in the cell instead of a drop-down list to choose from? For example, if I choose “Holy Avenger” in the first drop box, and the value for that choice in the second drop box is always a “2”, can you get it to auto-populate in the cell occupied by that 2nd drop box (cell B5 in the attachment)? Or, are you restricted to using Lists only, meaning that you could create a List that only has the value “2” in it, and the user would be required to choose the “2” in the drop box?
View 2 Replies View Related