One of the fields I import has a value like 100:01 or 10:01 or 01:01 or other combinations (the above ones are the most frequent ones).
Unfortunately excel always converts this field into a date and a time but this is not what I want.
Is there any way to stop excel doing this? I tried to work around it with the text property e.g. formating it to text after I imported the data and formating the entire sheet to use text before
I decided to format my data as a table so that excel will auto-fill my formulas when inserting new rows which works quite well. I have one snag though, when trying to insert a new row at the very top (i.e. inbetween the header and first row) and choosing Format As Below, it also applies Data Validation and Conditional Formatting to the header. The inserted row, however, is actually formatted fine and works well.
How do I stop this from happening, and why would Excel do this anyway (as it is effectively applying formatting to TWO rows when only one row is being inserted)?
The attached file contains hypothetical data on a basketball player's success from the free throw line. It lists the number of free throws attempted and the number made and then divides the number made by the number attempted to calculate the free throw percentage. However, I keep getting a # DIV/0! error in the games free throws weren't taken. How do I write the code so that when the error occurs, a message is displayed to the effect that no percentage can be reported because no free throws were attempted by checking the cell in column D? How do I use the IsError function to figure this out?
i am using the following code to close my userform and work book,this disables the exit button on the userform but i can still close the workbook by using the exit buttons on the sheet. can i disable the exit buttons on the sheet so the only way to close the workbook from the userform
Private Sub CmbClose_Click()
Dim ans As Variant ans = MsgBox("This will close XL, save current file?", vbYesNoCancel) If ans = vbYes Then 'save book and quit XL ThisWorkbook.Save Application.Quit ElseIf ans = vbNo Then ' no save and quit XL Application.Quit ElseIf ans = vbCancel Then 'no save, no close , back to userform1 Exit Sub End If
I already have a macro that on opening the workbook checks to see if the workbook has been renamed outside excel in windows and if so closes the workbook. I need a macro to intercept the save as command and prevent the user from saving the workbook as another name. I would however still like the user to be able to save. I would also like them to be able to save the workbook in another directory but only with the same workbook name.
I had a sheet that had a web query imported into it which took up columns A to F. The web query varies in length upon every refresh. In columns G onwards i have lots of formulas that read the information from the query to produce results.
When i imported the web query, on some refreshes it has the ability to push my formulas down in certain columns. I'm assuming this is because the length has changed of the web query maybe? Although it can do it on the opening import of the query which i find strange.
I was told the best way around this would be to put my web query on another sheet, however this doesnt appear to have fixed my problem.
Lets say for example #Sheet1!A1 looks at #Sheet2!A1, #Sheet1!A2 looks at #Sheet2!A2 and so on. This will work fine. When the web query is imported into Sheet 2, its data goes down to cell A72. Upon another refresh though, the length will changed and now the data might go down to cell A81. However, #Sheet1!A72 will have changed its formula to look at #Sheet2!A81 and my formulas on Sheet 1 will now read as follows:
I am trying to use the simplest code possible (being a novice) to execute a macro which makes a copy of Cell A1:A4 and paste its values to Cells B1:B4 if cell B5=1 (or whatever). The Macro with copy and paste works (I recorded it and in my testing it was ok). And also the function, that contains If-Then status of cell B5, works and calls correctly the Macro.
Yet when the macro is called by the function (=CopyMyCells(B5)), the Macro (MacroCopy) runs - I tested this with a Msgbox - but stops just in doing what I need, i.e. in doing the copy-paste process. Here the code which is placed in a module of the proper Worksheet (Excel 2003, on WinXPSp3):
I have made a vba program in excel 2003 that opens a worksheet using Workbooks.Open, and copies all the worksheets out into the program etc. The worksheets that I am opening have macros that automatically execute when the worksheet is opened (opening up some forms ). These macros are running when I open the file using VBA. How can I prevent this from happening. The automatically executed code in the workbook being opened is located in "this workbook" and can be seen below.
Private Sub Workbook_Open() Dim CfileName As String 'Check Config sheet for template state If Config.Cells(10, 3) = 1 Then CustomizeDirInfo.Show End If
If Config.Cells(10, 3) = 2 Then GetConfData GetGrpData StdFileGen.Show Else Exit Sub End If End Sub
I am needing to disable 5 worksheets from being calculated under the 'AutoCalculate' default option of excel. Currently, I set all 5 sheets' .enablecalculation property to false (see code example below), then save the workbook. This should prevent these 5 sheets from being calculated when the workbook is opened again - however these sheets are still being calculated with excels default 'Auto Calculate' setting once the workbook is reopened.
My issue: While having 'Auto Calculate' enabled, how is a sheet disabled from being calculated upon opening?
Current method of disabling worksheet - does not work tho.
At the time of opening the file some macros are running automatically in my file as per my requirement. If auto filter is on then it disturbs my program. Sometimes I forget to remove auto filter before closing the file and it cause a problem in my file after opening it again.
Requirement is: Is there any code to prevent the file closing if the auto filter is on and give me a warning message 'Please remove auto filter first'.
I currently have a tab that is contains a macro driven query pulling data from an access database. However, I have selected the option to add new rows for new data which in turn causes my sumtotal at the bottom of the sheet to be over ridden by this new data. My question is, could there be a way to have the total row move down as new rows are added from the query (formatting and all)?
When I export my files onto text and open it with Excel, it will automatically format the cells to take out leading zeros, convert to scientific notation, and convert certain numbers into date/month/year format. How can I get Excel to open my file without reformatting the numbers in my text file?
What I have is a database of measurements taken, where X-axis is distance across a surface and Y-axis is measuring deformation to that surface. Each measurement is actually a group of data points from one sweep across the surface on a certain day, resulting in a line plot for that group. What I'm trying to do is create a "checksheet" so that you can overlay plots from multiple dates on the same grid to compare how the surface has changed with each measurement. I've got the checksheet part working properly, and I can toggle things on/off as I like as long as my data doesn't change.
The rub is that this data is gathered via query, and each measurement doesn't have the same # of data points. If I update the query to look at a different date range, the plots are now off because the old data range doesn't match the new data.
Is there any way of changing the data range when a query gets updated, or a way to tie it to a function?
I have a spreadsheet with many rows of data. I want to be able to see the last month or so of data (each day is one row), but I do not want to have to scroll to the bottom of the worksheet to see the recent data, so I want to reverse the data - I want Row 1 to be my column headers, and for every new day I want to insert a new row at Row 2 and add my data. However, when I do that, my conditional formatting gets all messed up. Once the new row is added, the formatting rule changes its range to only those rows which had data in them before I did the insert, each new row gets a separate but identical rule that applies only to itself, and the headers row gets its own rule.
That seems incredibly stupid to me. Is there a way I prevent the formatting rules from changing and just stay the way they are which applies to the entire column?
I am trying to design a spreadsheet for planning a project schedule. Down the left are the dates, across the top are the resource names and in the content are the activities being carried out.
Each activity I have is given a two letter prefix, e.g. TD = Training Delivery. I then have conditional formatting applied to each cell to change the cell colour based on the two letter prefix. In all there are six conditional formatting rules.
Now, my problem is that I need this sheet to be really fluid. I want to be able to drag and drop blocks of activities to a buffer area at the side and then drag them into another part of the schedule. However, when you drag and drop (effectively cut and paste) the activities around the conditional formatting moves with the cut content, whereas I want it to remain where it was. I guess what I'm after is a way of dragging and dropping values only.
The best I've come up with so far is an onChange macro that reapplies the conditional formatting to the relevant part of my spreadsheet, but I can't believe this is the most elegant solution. Is there perhaps a way of triggering a macro onPaste?
I am auto-importing statistics from the web via web query. This information imported has a number in the form of a decimal stated as either x.1, or x.2. x.1 represents x-1/3 (one-third) and x.2 represents x-2/3 (two-thirds). I need to use these numbers in a calculation after importing them, but using x.1 or x.2 obviously does not give me accurate results as the numbers should actually be x.3333333 and x.6666666 respectively. What is the easiest way to convert the imported numbers to their actual decimals?...
My current solution is to filter the Master Log so I only see Region 2, copy everything, then paste over Region 2, and repeat with 3, 4 and 5.
I have a master log (attached) that I import using Microsoft Query to 4 other spreadsheets (called Regional Logs), depending on the contents of the column "Region." I have all this figured out, but I can't figure out how to have the highlighting persist through the export and refresh. I don't think the attached file has any highlighting, but how I use this is I edit the Master Log throughout the week and highlight changes as I go. Coworkers use the 4 Regional Logs throughout the week to monitor different cases, particularly the new ones, so they need to see all changes for the week highlighted in yellow, which means the Regional Logs need to be linked to the Master Log and the highlighting must be visible any time a coworker opens the Regional Log. In addition, they are emailed across the state to county directors and they need to see the yellow-highlighted data, as well.
Currently, when exporting data using MS Query, no formatting persists. How can I ensure the highlighting persists? Or, is there another way to easily export the data, depending on the contents of the column "Region", which would allow the highlighting to persist
I have a spreadsheet with several worksheets in it. The main sheet contains a calendar view that is fed from a separate sheet with holiday date ranges. This aspect works well and displays correctly in the main calendar view.
I am now adding in additional conditional formatting to each cell. As a first example i want the calendar to display bank holiday days by colouring the cell (lets say black). I am using conditional formatting only and have used the following formula;
Holidays is a named range, separate sheet (A2:A13). C6 is the date value field (runs C6 through AN6 - perpetual calendar)
It evaluates the cell (i can see, TRUE,FALSE,FALSE,FALSE,...) and will only shade the cell if the date value field is equal to the first date in the Holidays named range. if this is not the case (FALSE,TRUE,FALSE,FALSE,...) the field value is set to "0".
how i can change this, entered as conditional format formula, to set the cell to "1" if any are evaluated as TRUE regardless of where they come in the name named range.
I want to additionally add in, from separate worksheets in the same workbook, face to face meetings and conference calls. I have assumed that i will use the same method to do this as Bank Holidays.
I have to import data into Excel from Access (yes, I have to import it to Excel and cannot use Access exclusively). When I import; however, the formatting does not come over correctly, most especially losing the formatting of numbers. I need one of two solutions:
1.) How to get the formatting to move over correctly?
2.) Or how I can save the formatting of a table, then be able to apply it as a "saved" format? The biggest thing is that the numbers will changed the number of decimal places and/or change from general numbers when I want to see them as currency.
The top table shows how i want this formatted, the bottom shows how it is importing.
I'm tracking weeks by weeknumber in the format YY-WW. At the moment I am entering the weeks manually in a general cell, preceeded by a '. I would prefer to have the sheet work out the weeks for me as we move the sheet every week. I would like to format the initial cell as follows:
09-08 (or '09-08 if I must) then have the next cell in sequence read 09-09, 09-10, and so on. With the initial week in cell A3, I have tried to accomplish this with:
However, this does not work for weeks 01 through 09 because excel kills the leading zero in the second part of the formula, regardless of how I format the cell (format 00-00 does not work.) How can I force Excel it to keep the leading zero in the week number?
I have a column of cells that changes colour when certain text is entered into them, ie "TIME" will make the fill colour brown. However, when other text is entered into the cell alongside "TIME", the conditional formatting doesn't change the colour of the cell. How do i get around this so it picks up on the "TIME" text even when other text exists alongside it?