I am using Excel 2003, with Windows XP. I am monkeying around with a form to allow my boss to randomly assign employees to a daily task. There is a list of 15 names, but I'm just using the numbers 1 through 15 until I get the hang of this formula.
I am using MRAND, and the formula is working perfectly when I specify a set # of values to deliver. I wanted to see if I could allow him to enter the # of employees he wants to task in Cell A1 so that the number of random employees that are assigned varies with his selection in A1.
In other words, one day he could specify he wants 3 randomly assigned names to work on the task and the next day he could randomly assign 6 names/people, etc...
I thought I could just plug $A$1 in "maximum" in the formula below:
{=MRAND(maximum,1,15)}
In my interface, I have a listbox with the numbers 1 to 100. In my macro, I need to link the selection made in the listbox (from 1 to 100) to a variable. With that variable I perform simple calculations. Also, do I need to declare a variable for both the selection made in the listbox and the listbox itself?
Have searched previous threads but am unable to get a complete answer. I have a macro that looks up a reference number on a spreadsheet and assigns that value to a variable. What I am after is a way of displaying this varaible in a userform that is activated after the macro finds the reference number. I wouls like the userform to display "Reference number ######"
This works fine on the assumption that all the rows contain a value 0 or 1, this may not be the case as rows may be left blank for a varying degree of time and results may be posted intermittently?
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
I have a master spreadsheet being updated by several people, how can I/we, get a notification set up for when ever someone updates the master spreadsheet.....
I have made some changes to a workbook and now every time I want to close it I'm asked if I want to save the changes, even when I have made no changes. If I click 'Yes' to save changes the problem still occurs the next time it's opened & closed.
I have checked external connections and there are none. I have done a 'find' to check that there are no formulas looking at another sheet, which I cannot find.
This document has to have a live calculation function - I have read some suggestions around turning off auto calculations but this isn't a possibility and I'm not sure that this is where the problem lies.
The previous version does not have this problem, so it is something I have done between v2.2 and v2.3. I have attached both for reference. Note that they were originally xltm's not xlsx's - I could not upload macro-enabled templates, but the same problem occurs in v2.3 in this format.
I am trying to analyse, in a set of data that how many followup made by a salesman between a particular set of dates. I've attached a sample. In this example I differentiate between people if the name and age are same. I know that it is possible with formula 'countifs' but how?
The workbook I have has a tab for Master List of Transactions - there is data in columns A through J. In column E, when a year is entered, the data in columns A through D is copied to a tab for the specific year entered in column E. When the data from columns A through D is copied to the specific year's tab, it does not change anymore. If the information is changed at all on the Master tab, the macro does not currently work to automatically update the information on the specific year's tab but I would like it to. Is this possible?
Here is the macro I currently have:
Private Sub Worksheet_Change(ByVal Target As Range) ' Code goes in the Worksheet specific module Application.ScreenUpdating = False
Dim rng As Range Dim row_copy As Integer
[Code] ......
How to automatically update the year tabs for changes on the Master List of Transactions tab.
I have created the following in an effort to automatically goal seek whenever changes are made to the cell C2.
Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 3 And Target.Column = 2 Then Range("D19").GoalSeek Goal:=Range("C2").Value, _ ChangingCell:=Range("D3") End If End Sub
As far as I know, the Macro is running all of the time? When I use goal seek manually it works fine. Assuming the above is running, when I change the value in C2 nothing happens. Does the fact that I have a three sheet workbook make any difference? I am sort of lost. By changing the value in C2 I want Excel to change the value in D19 to match C2 by adjusting D3.
Application.EnableEvents = False Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 3 And Target.Column = 2 Then Range("D19").GoalSeek Goal:=Range("C2").Value, _ ChangingCell:=Range("D3") End If End Sub Application.EnableEvents = True
Doesn't seem to work either. However, I don't really know if other settings in the workbook are correct.
How do I select a shape I have just created with BuildFreeform function? It is not convenient for me to reference the resultant shape as I have done below:
Public Sub DrawDoor(TileType As String, Xpos As Integer, Ypos As Integer) With ActiveSheet.Shapes.BuildFreeform(msoEditingCorner, Xpos * 9 + 1, Ypos * 9 + 5) .AddNodes msoSegmentCurve, msoEditingAuto, Xpos * 9 + 5, Ypos * 9 + 1 .AddNodes msoSegmentCurve, msoEditingAuto, Xpos * 9 + 9, Ypos * 9 + 5 .AddNodes msoSegmentLine, msoEditingAuto, Xpos * 9 + 9, Ypos * 9 + 10 .AddNodes msoSegmentLine, msoEditingAuto, Xpos * 9 + 1, Ypos * 9 + 10 .AddNodes msoSegmentLine, msoEditingAuto, Xpos * 9 + 1, Ypos * 9 + 5 .ConvertToShape End With Shapes(8).Fill.ForeColor.RGB = RGB(200, 90, 20) End Sub
I want to give the shape a name, without having to use Shapes(8) at any stage. Is this possible when making freeform shapes?
The first column is "Person Name" and the second column is date in format "6/27/2013". I want a formula to calculate the total number of calls a person made in the month of June.
i bought a tattslotto scratch card (for those of ya outside of australia, not sure what youd call em, basically lottery ticket/game), this game is essentially a crossword puzzle, you scratch your letters out in a box then you have a list of words, you need to find words that are made up entirely of your letters, the more words you find, the more you win ive kind of screwed up the card and scratched out words i dont have, however i do have atleast 2 words and i think ive got more, potentially could win big but wanna know how much before i go claim it
ive opened up a new workbook, in the A column ive got a list of all my words, then in C1 ive got a field where ive entered all my letters, wondering if theres a way to get excel to work it out for me (like an IF statement on the fields in column A to compare them to C1, or some kind of conditional formatting rule)
I made a command button on the worksheet from the Tool box. This is not made on a form. When I click on the button I would like to get focus set to a certain cell and made active on the same page. Like cell H2 for example.
How can I search a range of cells that have a date entered by hand for a certain month and then sum the amounts given in another range of cells. For example, search several worksheets for payments made in September and then return the sum of those payments.
I've tried to do conditional formatting for each of the "Discipline" values in this spreadsheet but it is restricted to only 3. Is there a way around this? I'm trying to setup a color code for the "Discipline" values for example RN=Red, LPN=Blue, Social Worker=Orange, Chaplain=Purple, STNA=Yellow, Volunteer=Pink & Other=Green. So when the "Discipline" cell/cells on each of the patient sheets (ie. "Doe, Jane", "Doe, John", etc.) is equal to say RN the cell would then turn Red either on the spreadsheet or only when sheet is printed.
how to write a custom made IFERROR function which works on Excel 2003 that mimics as closely as possible the Excel 2007 IFERROR function without using any array formula.
Is it possible to detect when a auto-filter selection has been made ? Is that when an autofilter hides rows, the activecell could become hidden, which potentially may cause errant data entry, not knowing what cell is receiving entry as it is hidden from view.
if the worksheet contains a change event that autosizes the target row, it unhides the filtered hidden row in the process. I could say if target is hidden ignore autosize, but i would prefer to activate a visible cell (first visible cell for filtered column) on selection of autofilter.
The intent of the code below is to run the marco HideCol when a change is made in cell C10. If I actually change the value in C10 it works. However, I have C10 linked to another worksheet (=anotherpage!A13). When the value changes, my macro is not executing.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim changed As Range Set changed = Range("C10") If Not Intersect(Target, changed) Is Nothing Then HideCol End If End Sub
I have been working on this data entry and validation tool for work for a while now. I've got most of it down and works just as I want it to. However, I have made some validation lists for people to select and animal ID when entering some data. Once the animal ID is entered I want to remove it from the source data and the list to stop people entering a duplicate number for the same event.
I'm not sure how easy/difficult this will be but the whole reason for this project was to stop people entering the same data and only allow them to enter data that I pre-define. Its to cut down on mistakes where somone will enter the wrong ID and then finding the correct ID becomes difficult due to having over 4000 animals on site.
Count number of occurrencesI have a large spreadsheet that list customer and call dates. Each call is displayed in a separate row with the corresponding customer next to it - so each customer may show up multiple times with different call times. I want an equation that will count if 4 calls (occurrences) have been made to each customer and if it is the answer is "1". I have been trying to make it working with COUNTIF but not getting the results.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
... to capture when a user is about to close a workbook. I then give them a message: "Are you sure you want to exit? Changes you made since you last saved will be lost."
However, to make it neater, is there a way to only trigger the macro if they have made changes since the last save? In other words, if they have not made any changes since the last save, the message will not be displayed, since it is irrelevant.
