When i enter a value in an excel sheet cell ,Excel auto detect the data type and format that cell to that data type.
For example if i enter 4/4001, excel read it as date and convert the cell format from general to date and displays 4-Aprl.
However if i precede with " ' " this problem is solved but my question ios that can i permanently disable this auto detect option?
I want to know what is the way to auto save our excel sheet data ?
View 3 Replies View Relatedi want to type 1-1 in my excel sheet and it is automatically coverting to 1-Jan and then i checked for formatting to the way i want then i founf excel automatically selecting custom option in format then if i select general my 1-jan is converting to 41640 number. Now i want excel sheet should show what typed in it i .e. 1-1 only.
View 2 Replies View RelatedI'm working on a workbook that has alot of calculations in it, on a pretty slow PC (Work-related), so auto-calculate is casuing crashing, so what I've done with this sheet is disabled auto-calculate and added a macro to calculate each sheet indiviually instead, which avoids the crashing (The last sheet still takes a minute and a half to process but thats ok I guess).
My worry is, when this workbook does the rounds, peoples excel will already have autocalculate on, and will crash it themselves. Is there anyway I can ensure that the sheet turns atuocalculate on or, failing that, does anyone have any ideas on how to help the issue?
I ran into a problem with one of my macros the other day. It processes several data files given to us each week. This week, one of the files had Auto-Filter enabled. I had to manually disable auto-filter, and re-run my macro.
I tried recording this action, but wasn't able to. Is there a way to disable/enable Auto-Filter with VB?
My Excel spreadsheet is linked to an Access database to Get external Data. When the data is periodically refreshed ,while the spreadsheet is open, I need to automatically detect it so that a macro can be run each time to format the new data.
The Excel data is not a pivot table. The spreadsheet is for display of data only (no user updating).
I was wondering if excel can auto label peaks and bottoms of a graph? Let me go further:
I have a graph that has one data number for every day of the year (365). This is a natural up and down curve, similar to a stock.
Can excel find the key high and low points? I have attached an example. In it you will see I have quickly labeled the key tops and bottoms. CHART TEST.xlsxCHART TEST.xlsx.
I basically omitted the peaks/bottoms that are very short term and irrelevant.
I have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).
Excel 2013
System is generating IDs like 1-OCTO33, 1-NOVE44, which on exporting in excel converts to 1-OCT-33 & 1-NOV-44. How can this auto-formatting be restricted in excel?
To test, you can try entering in excel 1-OCTO33, it converts to 1-OCT-33.
I am looking for some setting in excel, which would prevent it on exporting to excel. Other options like changing column format to text or using a limiter ' will not work in this case.
Is there a way to disable the Excel print button and force someone to use a macro button to print the spreadsheet?
View 1 Replies View RelatedIF I enter 01-09 in B10 or B11 or B12 want autofill FIRST in C10, C11, C12
11-19 in B10 or B11 or B12 want autofill SECOND in C10, C11, C12
20-29 in B10 or B11 or B12 want autofill THIRD in C10, C11, C12
HOW TO DO THIS ?
I have been modifying a workbook and the original macro will send to the sheet Results once the "Search" button is clicked, but I have found that this macro is displaying duplicate records. Can anyone help me put in an auto filter to find only unique records? My second workaround option is if someone can help me remove the go to/select sheet option from the "Search" macro so that a user will not automatically be sent to the Results tab and will instead hit the "Confirm Category Selection" button (which auto filters before sending the user to the Results tab).
View 12 Replies View RelatedIs there any way for excel to automatically choose the next best spelling option for many rows of data containing mispelled words? For example, is there a way to automate pressing "Change" on every mispelling window when pressing F7?
View 8 Replies View RelatedWe have a huge excel-sheet, containing disputes with customers. Everytime there is a dispute, the customers data will be transferred into the sheet. The sheet is organized like this:
DATE / NAME / ID / COMMENT / FREQUENCY (how often one customer appears in the list) /ID2 (ID combination of the date + ID) /Weekday
And here comes the task:
Find all customers who appear 10 work-days(Mo-Fr) in a row. So if a customer appears Mo-Fr and keeps on being in the list the next Monday etc. it still counts as "in a row". Same with official holidays. If a customer appears at a certain date he is "booked" and it doesnt matter if a appears a second time on that specific date.
Attached to this post you find an example in which you can see the structure of my excel sheet!
example.xls
Note: I had to use a german version of excel when I made this little example - thats why the formula in the collum "Frequency" says "Zahlenwenn" its the german version of COUNTIF
Excel 2003
I'm designing a workbook to track psychiatric patient data. The workbook has identical worksheets for each month of the year (Jan, Feb, Mar, etc.), plus an Admin sheet for overhead. I need to devise a formula that will automatically detect 30-day readmissions (i.e., patients being admitted to the hospital within 30 days of a prior admission) and return a "Y" for a readmit and an "N" for a discharge or first-time admission. I have tried several variations on SUMPRODUCT with multiple conditions spanning multiple sheets (using INDIRECT and a monthly worksheet index -- A1:A12 -- on the Admin worksheet), but the results always turn out...wonky. (Sometimes a correct value is returned, and sometimes not. I can't figure out what is causing this inconsistency.) The relevant ranges from the monthly worksheets are:
B3:B52 : Date of evaluation
H3:H52 : Unique patient ID number
Y3:Y52 : Patient disposition (i.e., admitted or discharged)
AC3:AC52 : Readmission status (i.e., yes or no)
The formula should only return a "Y" value if a) the patient was previously admitted within 30 days of the evaluation date, and b) the patient was also admitted on the evaluation date.
I am using Excel 2003, so SUMIFS is not available.
There are some excel documents in a hidden folder that contain sensitive information. Shortcuts to these documents will be provided to users to open them and add data. These documents contain all kinds of macros as well. Once macro disables the 'Save As' feature so that the workbook can not be saved outside of the hidden folder, preventing to some degree a user from saving as a different name and emailing the document with the sensitive data.
This all works fine with Macros enabled. As soon as macros are disabled the 'Save As' prevention is bypassed and the workbook can be saved anywhere.
Is there a way for an administrator to lock out the Trust Center options for disabling all macros?
is it possible through vba code to determine the macro security level of Excel 2007 and inform the user. I tried the code below , it is executed when the workbook is opened, but it doesn't work.The code below is executed only when the macro security level is "Low". My goal is at least to inform , with a msgbox function, to change manually the security level.
Code:
Dim secAutomation As MsoAutomationSecurity
Dim zLevel
As String
secAutomation = Application.AutomationSecurity
Select Case secAutomation
[Code]....
I have many shortcut words to make full name using Auto Correct option under Proofing
I am using this code to make force range in Uppercase
[Code] ....
But when i put a auto correction value in small case "ip" {like ( IP = IRFAN PAT )}
Then the cell value is going in uppercase "IP" but auto correction is not working
I want ..if i type in range "ip" result shoul be (" IRFAN PAT ") not "IP"
I have a userfrom with 2 textboxes used for entering dates. would like to either force the user to enter the date as xx/xx/xxxx or have a calendar feature where they can select a date. I checked my 'additional controls' in my toolbox but do not see a calendar option.
View 1 Replies View RelatedI've a excel workbook (2013) with aprox. 10 sheets (will include more). I want a macro code which search with different criteria.
when I run that macro a popup box will appear with criteria option, when I fill the criteria macro search that in entire workbook and show the result.
I have hide workbook in view and hide option, when i unhide the same it should ask password. Is it possible.
View 1 Replies View RelatedI am pasting output from SAS into a spreadsheet. The data I am pasting cannot be put into columns beforehand. When I do text-to-columns, the data are auto-formatted. This is how it looks before text-to-columns:
159 xx Char 2 2. $2. xx
This is how it is auto-formatted:
159xxChar22$2.00 xx
The loss of the period [2 to 2.] and the addition of zeros [$2. to $2.00] is a disaster because it makes my SAS code non-functional. Is there anyway to stop auto-formatting in text-to-columns? Is there a more user-friendly software besides Excel that would be better to use?
I've got a simple macro for asking a user to locate a file of choice and inserting it into specific cell.
Is there a way I can code it so that regardless of the image it 'fits to fill' the cell? The images may vary
The size of the cell will be fixed, but I don;t know who to ascertain that information or tell VBA what it is?
Sub InsertLogo()
Range("A1:E5").Select 'merged cell'
Application.Dialogs(xlDialogInsertPicture).Show
'Object.AutoSize = True
End Sub
I tried using the 'Object.AutoSize = True, but kept getting stumped on a run time error.
I am using countif formula to count number of times each name appears in a row, but when I use the filter opeion and select a specific name, and highlight the whole row, the 'count' at bottom right of excel screen does not show same count as the outcome of countif. why is this so?Shouldnt they both be the same?
View 4 Replies View RelatedThere was an option in excel 2007 in the pivot wizard to use an existing pivot table report as the source for a new pivot. I cannot find this in Excel 2010.
View 2 Replies View RelatedHow to supress Enable Macro option while open a excel file. I would like to do it using VBA in Macro of excel 2007 + versions.
View 9 Replies View RelatedI currently have a macro setup that puts Auto-Sum data in the row below the bottom-most row of data. What code would I need to format this Auto-Sum row with the following parameters automatically...
1. Merge & center F & G in the Auto-Sum row with the text "Totals".
2. Change the row height of the Auto-Sum row to 44 pixels.
3. Center the vertical alignment of the Auto-Sum row.
4. Put the thickest border around the Auto-Sum row in cells A-K.
I have three columns in Excel
Column state "5K", Column B state number in text format, Column C state text.
I would like to auto extract the number in below sequence by sorting "Column B" to Column D format.
How to do it?
Text
Text
Text
Column D
5K
01
B
5K01B-5K03B
[Code] .........
I am having trouble with using the auto filter, and I suspect it is due to my data format.
I am importing data from an outside source (I have no control on how the data is output). The data in the column I am concerned with contains various formats, such as numbers, letters, and a combo of the two. e.g.:
12345
sample
sam123ple
I have been taking the entire column and formatting it as text to make all of the data consistent. The problem I see comes when I go to use the autofilter, to say custom>begins with "123". The value 12345 does not show up in my filter. However, if I double click the cell 12345, then hit enter, and re-run my autofilter, it shows up.
Does anyone know the reason why this double-click-enter method forces Excel to recognize the value? And is there a way to mass-produce this command for the entire data set? Right now, I'm up to 5000 lines of data, so to do this on each would take a while.
I am trying to make a column of names appear in upper case (the first letter). I know about Upper(). I cant put that formula in this column because the names are typed there, (it would erase formula). How can I do this as well as have them alphabetically ordered?
View 2 Replies View Related