How can I have a form field automatically fill some aspects of the data that's input? I've seen it before, like a phone# field automatically including the dashes.
I would like to automatically include colons in a time field. So if I were to type "081500" (or even "81500"), it would complete it as "08:15:00".
I have cobbled together (borrowing from examples I have found online because I don't know VBA) a form which inserts a date into a spreadsheet.
The trouble I have is that this date field is in USA date format and I need UK.
If I enter 03/01/2009 into the form, it appears in the spreadsheet as 01/03/2009. I have formatted these cells to display the month only and because of the way it was entered, the next column displays MAR instead of JAN.
Can I validate the input value in a form?
I currently have a pivot table linked into a excel query. This pivot table is refreshed everyday and looks at the following data:-
PAGE - Username
ROW - Date
DATA - Output
What I need to do on a regular basis is select the pivot table field Username and hide specific staff which are new to the table.
My question is can i create a code which will only show usernames from a given range e.g. Sheet 1 A1:A10 in this field?
I create a Pivot Table in Excel 2003, excel by default puts the field settings for each of the columns to Automatic, creating a Total for each aggreate column, which is very annoying.
I have to manually go in in each field and change field settings > None. for each column, since no copy and paste special function to make all the columns have same subtotal >none.
I have a scroll (form control) linked with data from another sheet. It scrolls nicely and works fine but I'd like to be able to update the data sheet and then the table with the scroll automatically update too.
View 9 Replies View RelatedI have a userform that has a time field on it and then the userform displays the time as a number.
Ex. I entered 11:00 AM in the Time of Contact field and the form displays it as 0.458333333333333.
How can I allow users to either select the SKU from the dropdown menu, or to type in the SKU # in the dropdown menu (C5 through F5) and have it populate the data fields for that SKU when they press the 'Enter' key?
Currently using Data Validation ='KVM Comparison Data'!$B$4:$OP$4 and cell logic to allow the information to be pulled from a hidden tab =INDEX('KVM Comparison Data'!$D$2:$OP$65,MATCH('KVM Product Comparison'!$B6,'KVM Comparison Data'!$A$2:$A$65,0),MATCH('KVM Product Comparison'!C$5,'KVM Comparison Data'!$D$4:$OP$4,0))
In short, I want to keep it the way I have it, but allow users to also have the option of typing in a SKU and pull the data up that way. I know this is possible, I just can't figure out how to do it, while keeping my current solution in place as well.
I have a text field at the bottom of a user form that remains hidden (i.e. visible = false) until the user clicks the "Ok" button. At that point, I want the text field to appear as the macro is running (it's a large macro, so the text field just says "processing, please wait...").
The first line of my macro is:
Is it possible to set the input Range of a dropdown control to be a column from a table?
View 2 Replies View RelatedI have a Form that user fill in information on, once they click the OK button, excel is filled with the inserted text on the form in the correct cells.
My question is, how do I validate that they have actually entered data in some cells, which I want to make mandatory, and if they have not, prompt them (this could be a simple message box) to fill it in. Let them fill it, and once they click OK again, check again... until all the mandatory fields are filled, only then will the macro fill in the excel cells.
When I type a date in, e.g. 01/01/07, the result is 01/01/2007.
01/01/28 => 01/01/2028
01/01/29 => 01/01/2029
01/01/30 => 01/01/1930
01/01/31 => 01/01/1931
Excel resets itself to '1900's' after XX/XX/30. Is there anyway to change this so any year I type will always be '20XX'?
I need to control the amount of decimals allowed by the entry of another column same row so I do not have to format the cells everytime to get the correct decimal places. I need all my data in one table so I can do pivot tables.
View 14 Replies View RelatedI was curious if there is any way to have time automatically tracked in excel. I will have a few colums, first will be the time, second will be notes, when a note is inputed is there a function available to automatically fill in the time?
View 2 Replies View RelatedI can input the date and time automatically in the worksheet while a enter a number in the 3 column but when ever i enter number in the next cell whole time is changing in all the cell and it showing the same time for every number in the entering and not different time in the columns
View 9 Replies View RelatedI am looking to create a spreadheet that will automatically shade blocks in a gannt chart according to a list of dates.
I.e B13 & B14 will have the start and end date of the particular task or activity (this will be on another sheet)
Row A3 - S3 will contain dates, iusing a lookup formulas excel will look up the activity name (or number) and look for the relevant date, if the date listed in row 3 is within or equal to that date it will return yes, if not a no.
this seemed straightforad, but I cant think of a consistent formula that will do this, without having to keep changing it ...
i am trying to make one cell automatically change to current date and time when the cell next to it has changed value. ive search other posts but cannot figure this out. so if cell A1 changes value cell B1 should automatically fill with current date and time cell A1 changed value.
View 9 Replies View RelatedI have a time tracking sheet. In Column C and Column D there is a start time and end time respectively where my team people will enter the current time accordingly.
I would like to lock the cell once they enter the time but the entire sheet should not lock. Only the Column C and D should lock upon people enter the time. Basically, once user enter the time, they should not be able to change it.
How can I get an automatic time & date entry into cell B1 based on a alphanumeric entry in cell A1. The time and date must not re-calculate every time the workbook is opened.
View 4 Replies View RelatedI use ADOBD.Connexion / Microsoft Jet SQL to connect to Excel tables and create a pivot cache.
I need to understand the required SQL syntax to define a field format.
Exemple:
SELECT Table1.Field1 FROM Table1
And I need Table1.Field1 to be treated as text
How can I set the format of a field in my SQL statement?
So I've made a large spread sheet in Excel that calculates many values and constraints for the optimization of a structural beam; from there, the minimum value that satisfies all constraints is output in an individual cell. I want to see how this output value changes when I change one value, the length of the beam. For instance, I input a length of the beam (10 ft.) and I get an output value of 643.50; I copy and special paste this value manually into a cell. I then change the length to 15 ft. and get an output value of 1322.83 and copy and special paste this value into the next cell. This process is very tedious, because I have to do this for hundreds of values. Is there a way to automate this so that I don't have to do everything by hand. The tricky part is that every time I change the length, the entire worksheet must recalculate before I get a new output value.
View 1 Replies View RelatedIs there a way to automate the date and time in one cell upon saving? I have seen some posts elsewhere upon this subject, but here is the twist:
this is a shared workbook with each tab allocated for a different person. Each person needs to update their worksheet and have the date and time update upon saving. The date and time would appear in the same cell for each sheet. I am fairly certain that this cannot be done with an Excel formula, so I would appreciate if anybody had any VBA suggestions. (I am just starting to learn VBA
on data import from CSV file. The file is formated in a sligthly strange way:
"London","182.56","12/12/2007"
"Paris","133.41","27/12/2007"
I get into problem when importing using default CSV settings as numbers and date are understood like text. I need to replace "." with "," to get to number format. Used following Range("J5:K9999").Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
However I get values multiplied by 100 for some reason.
Converting date format gets eeven more complicated.
So I decided I can not use standard CSV import and need to define separate data source for each raw file This is not what I wanted, since I do import many files (10 at this point in time, but it will get growing). However, I would be ready to do that if there is no other option. So I go to "Create New Data Source" and under "Connect" I get to another input box called "ODBC Text Setup" where I select "Options" to define customer format. However, after selecting source file, Excel is not able to interprate fields. Tried clicking "Guess" button, no luck there. Thought it is because fo those "" before and after field value. So, just for a sake of experiment I opened file in text editor and removed those ". Still, can not read the file. The error message says "Text file specification field separator matches decimal separator or text delimiter. Guess failed"
I am out of ideas how to get this file properly imported. Hope there is a way to do that either by defining field formats while importing (perhaps that can be done via SQL, but I am not confident with it) or writing some code to change format after importing from text to Number and Date...
I need to sum the Time field, if the Temp Zone is = F. My problem is, as shown on the sheet, there are multiple days. As well, I need to pull the information for the totals (looking at 6 days worth of info) into another worksheet where the names (first column) are not in the same order, as some of the names are on this sheet, and some are not. I had a massively large vlookup statement, but of course, it's rather large and doesn't copy over well (when changing the days, it looses the last couple of lookups) ......
View 20 Replies View RelatedI have some intraday date that looks like this, hope the formatting comes thru when i post this....anyhow, the second column is TIME (ie 15:25 15:30, etc...5 minute time intervals for SP500 stock data. I want to have a column that simply subtracts row 2 column b (time column) from row 1 column b.....when i do this i get a #value....I know this must be possible to get a result i need....ie 15:30 - 15:25 = 5
Do i need to reformat the TIME column into something different? ....
I am using excel to compare data from one spreadsheet to another. However my utility is not capturing identical matches since one worksheet eliminated the leading 0s while the next worksheet includes all 0s. Our system should actually have the 0s to identify the data correctly.
Worksheet 1
M10235
Worksheet 2
M000010235
I would like to convert Worksheet 1 to show M10235 in the format M000010235 so that the number contains the prefix M followed by nine digits.
I have a spreadsheet with a column full of dates
I used the formatting wizard to make all dates the format of dd/mm/yyyy but, now, when I put a date in the column it throws up a number '41313' instead of a date, even if I manually input the date in the same format as above.
Also, I have tried to turn cells back into general number fields, but this doesn't appear to be changing it back to a date format..
There are HUNDREDS of dates I might have lost if I can't get them to start showing as dates again!
What i need is to be able to concatenate a field such that it includes current system year,month,day and time. My attached spreadsheet illustrates my current formatting to achieve year,month,day. But I seem to be challenged in getting the current time to display properly.
View 4 Replies View RelatedI have a list of dates with an hour inside them . I am trying to remove teh hour from the field , and leave only the date. teh format option just hide teh hour but not removing it how can it be done ?
View 4 Replies View RelatedI want to display a date and time in local time, is there a simple formula to do this?
View 3 Replies View RelatedI have data that I received from a feed. Each cell displays a value like 100, 200 or 300 or a time like 10:41:52 AM, but the formula bar depicts the array information ie =Q|TS!'AVAVA,45000,DEIPSCT'.
I'm working with the data where basically the time is the independent variable and the number is the dependent, very basic stuff BUT I cannot figure out how to reference the time.
Just trying to write a very simple if function such as =IF(G17="8:50:17 AM","start") and I can't figure out how to do it. Cell G17 is showing 8:50:17 AM.