This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following: Within a workbook the 1st sheet is the data entry. In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example: Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry. G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
I have 6 pivot charts within a document. I use a macro associated with a button to refresh the tables and therefore the data displayed on the charts.
However, each time I refresh the data, the 'data label' font size also refreshes. What is particularly odd is that 5 of the charts are pie-graphs, of those 5 graphs, the font size only resets on 4 of them (with the data labels in a single chart staying at my desired font size).
I can manually put the font size back to that which I desire but I'd rather it just didn't change in the first place.
I've had a look to see if there's any noticeable differences between the 5 charts but there's nothing which I can find.
I'd be happy to run a bit of VBA to set the font size as I want it, if there isn't a better solution.
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
I'm not sure what it's called, but in a workbook that was sent to me, when a cell is selected a text box appears with instructions in them. This is not a normal comment and I found that I cannot select it, right click it, or anything to delete them. They can be annoying as they cover up other data when the cell is selected.
number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.
I'm developing a series of schedules that organize building plans. In one schedule I maintain a list of areas (called spaces, like a lobby or a conference room). I programatically maintain the list of spaces in the schedule for sorting and other external functions that are irrelevant. In the process I occasionally delete a worksheet. My worksheets have 4 buttons and a combobox. Whenever I delete a worksheet with these objects, I lose ALL of my module-level variables. These include two collections, a boolean, and an integer.
I'll post only the code for now. If you want to replicate, remember to place at least one button or combobox on your worksheet. You'll need to execute each sub manually. I have placed debug statements to print the globals to the immediate window. Notice that the 3rd debug statement shows the globals as present when it's called from the delete routine; however, if you manually execute that print routine after the delete, the globals are gone!
Option Explicit Const SHEET_TARGET_RANGE As String = "C13:K36" Dim m_objLocal As New Collection Dim m_objMaster As New Collection Dim m_bln As Boolean Dim m_int As Integer Public Sub Debug_Populate_Globals() Dim intLoopCounter As Integer Set m_objLocal = New Collection Set m_objMaster = New Collection For intLoopCounter = 1 To 8 m_objLocal.Add intLoopCounter, CStr(intLoopCounter) m_objMaster.Add intLoopCounter, CStr(intLoopCounter)....................
I need a formula that will show the total number of times "text" is entered into a column and when sorted by year that would only show the total for that year. The COUNTIF formula i'm using only works for the total, not when sorted by year.
I have three columns of data (A,B,C) and I want to add a formula or script to create the data in column D. The 'XXX's are all numerical values and will vary.
Column D should be a sequential list, which restarts at 1 any time that either: - the value in column A changes, OR - column A stays the same, but the value in column B goes from something to nothing, together with the value in column C going from nothing to something (or vice versa).
I have a macro i would like to run whenever a user deletes a worksheet...
I would like the user to be able click as normal to delete,(ie. right clicking the tab and selecting delete worksheet, or selecting delete worksheet from the menu) but would like to run my macro when they select delete.
I am using excel 07 if that helps, or i could use another version...
My macro will save a copy of the deleted worksheet as xlveryhidden as a backup..
Complete List of People in Column AA. Partial lists of these same people in columns A, C, E, G, I, K. Goal: Once I put that persons name in A, C, E, G, I, K, I would like it to be deleted from Column AA.
I have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
Macro works fine if i manually enter a time ie: 14:00 into cell a5 but it only sees a5 as text not as a time format how do i change it so the macro is looking for time not text entry?
Code: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$A$5" Then Dim raceTime As String raceTime = Target.Value If raceTime "" Then
I have a very large exported data base to excel. See the Attachment. I have columns on the left with text that needs to stay as text, and rows at the top with dates. These are my filters to sort and view my data. The current problem I am having is some of the quantities in the bulk of my spreadsheet are coming in as text that need to be converted to numbers (that stupid green triangle in the upper left hand corner). I already have developed a macro to format, sort, extract, and place the data so it's more manageable to look through. I need to get rid of this convert to number error before running this macro.
I am looking to develop a macro that will find each of these "convert text to number" errors and convert the text to number. So far I have been unable to find a starting point.
The sample attached is only 1% of the actual size of the spreadsheet, and my exported data base is variable in size.
The only solution I have at the moment is to go to the bottom of my spreadsheet, find the last error, and highlight everything above and done one convert to number.
I have a range of numbers stored as text. I need to convert them to long numbers using vba. I tried typing a simple number = clng(a2), but that didnt work. Can someone help me with A) the right formula and B) the proper end(xlUp) format.
I am looking for a way to show the character location number of a text file, possibly in the first row or a macro that I can run at any given location that will give me the location # I am currently viewing.
Currently, when I open the file I can see the character # at the opening screen (see attached file) but they disappear when it actually converts. I would like to be able to keep the character location ruler once the file is opened in Excel so I don't have to manually count.
1000 Susan Y Y Y Y Y CountA(B6:B12) 899 Ted Y 1560 John Y Y 1000 Susan Y Y etc.
I need to be able to total the number of Y for a given person in a week as well as a total for the person over 4 weeks. CountA works for the week But what formula would I need to count for a specific person over 4 weeks. Col A is in random order. If the person has no Y the answer needs to be blank and not N/A.
I have a table with 5 columns and approx. 85-90 rows.
Column A has the Branch name in it e.g. Beavers or Bedfont (11 Branches in total) Column B has User Type - Adult, Child, Guest (Adult), Guest (Child), Catalogue Column C has Session Type - Booking, Drop-In Column D has Total Session Time (mins) - which gives a number in minutes of the total session time used Column E is not needed
I currently get a calculator and add up e.g all of the adult Bookings for Beavers and enter them onto a Report Sheet, then all of the Adult Drop-Ins for Beavers etc. I want an Excel Spreadsheet that will give me a total number for each so I can do away with the calculator.
I am thinking of creating a new sheet with a number of cells that have a formula similar to this
I am creating a spreadsheet for inventory use. I want to have a running total in (1) cell based upon a new/different number being entered into a different cell.
Column B, Row 1 (This will be a new/different number entered every day - inventory in or out, so positive or negative number)
Column D, Row 1 (This will be a running total based on numbers inserted in previous 2 columns)
Basically what I have is a key inventory. So there is column A with key number, B should be keys IN, C should be keys OUT, or ideally B would be IN and OUT meaning, for inventory IN input a positive number and for inventory OUT input a negative number, and D Should be total.
Column E represents number of keys currently in the inventory. I was going to hide the current inventory column so all you see is IN/OUT and total. So what I need is to be able to just come in and type in a number in the IN and/or OUT column, without having to add/subtract it with the number already in that column.
Bottom line, I'd like to be able to use IN and/or OUT columns to just type in numbers as they come and not have to worry about what's already in those columns and get correct total number.
Ok here it is. Attached worksheet shows Column A - Key Numbers...No data value Column B - Inventory IN Column C - Inventory OUT Column D - Total Column E - Current Inventory(Starting point)
So the formula I used to get what i currently have is (=B2-C2+E2). This way whatever i input in columns C and C. totals out in D. But this way every time I want to add/subtract a number in B and C, i have to add to the number already in the column. I'd like to be able to type in a number in B and C as i go and still have a correct total. I wouldn't mind having just one column for in/out and use positive and negative numbers to differentiate inventory in or out.