I have a speradsheet for my work, which gives the user problems and solutions to our website. however I cant get the userform to recognise the named ranges I have added. (ie the first combobox wont recognise the rowsource, which is a named range).
I have created a spreadsheet that is a roster to track mutliple crews and shifts.
I have a userform which is used to select a person on shift and assign them as being on either, Annual Leave, Personal Leave, Training.... on particular days.
I would like to have the shift combobox auto populate what shift the person is on based on the person and dates entered. All the information is there I just can't get the code to do this.
I was also thinking about blocks where the shift goes from DS to NS and if people are on leave over this time. I would need the combobox to know this also.
I have attached my workbook : New Annual Leave Spreadsheet.xlsm
I have dynamic named range that lists by columns. I can use this with data validation pull down on my worksheet and all is fine. But when I use this named range in the RowSource of a combo box in a userform, I only get the first column of data, or basically, the data in B1, but not C1, D1, ect. Here is the formula for the named range.
=OFFSET(INFO!$B$1,0,0,1,COUNTA(INFO!$1:$1)-1)
I have a range of data that is compiled from various cells that I have named as Range1. I then have another named range - Range2 which should be items selected from range1 and copied into range2. This is to enable other work to take place.
I have created an example userform with Range 1 shown but do not know the code to copy it over using a command button. Range 1 should not change. Also vice versa if possible i.e. remove the selected item from Range2 with another button.
i have a userform with a text box in it a i would like to display all the values in a named range called "emp1"
View 4 Replies View RelatedI'm trying to add a dynamic named range to a combo box in Userform but getting this error.
Have tried several lines of code this being the last that hasn't worked . . . .
Code:
CustCBx.RowSource = Range("CustList").Value
This is the formula for the named range in sheet
Code:
='Customer Info'!$J$2:INDEX('Customer Info'!$J2:$J200,COUNTA('Customer Info'!$J2:$J200))
I am using the following Selection_Change Event to show a UserForm when a cell in 1 of 31 named ranges is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim i As Long
For i = 1 To 31
If Not Intersect(Target, Range("StatPost" & i)) Is Nothing And Target.Value = "" Then
If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then....................
Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:
Code] .....
here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
View 2 Replies View RelatedI'm trying to make my named ranges remember the values of the last active cells used within another named range. The purpose of this is to make my charts dynamically change dependant on two criteria selected. My spreadsheet currently updates itself as and when I change the active cell within a single named range, dynamically changing the chart data by using Lookup based on the active cells value. However I want to get away from having several charts showing, I would like to have a single chart which dynamically changes based on a second selection. So the first selection is for a department (Facility) which changes the chart data relevant to that department, the second selection is to dynamically change the chart shown for the pre selected department.
Picture2.jpg
Using the following code when updating just one criteria with several charts
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, [MeasureType]) Is Nothing Then
[valMeasurePicked] = ActiveCell.Value [code].....
which works fine but I'm not sure how to add a second selection criteria because my code uses Activecell. I thought that the VBA needed to set the last used value of a range as a variable and therefore allow the second criteria to be selected but am not sure how to put it into practice.
if I can use a named criteria as well as a named range. In essence what I am looking to do is count certain cells that meet the criteria in a certain named named range,
View 9 Replies View RelatedMerge two columns into one list in excel
I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).
Here's the formula from the example:
Code:
=IFERROR(INDEX(List1,ROWS(C1:$C$1)),IFERROR(INDEX(List2,ROWS(C1:$C$1)-ROWS(List1)),""))
I've played around with it, but could not come with any that worked.
I have a drop down list in rows of a column to fill out a ledger. The items have multiple account number that I want to put in a second column. I can do the lookups, but I want to have one cell that equals the value picked in the first column regardless of the row. For example if I pick a value for A1 I want it to equal O1, and if I pick a value for A2 I want it to equal O1 and so on, in order to activate a lookup for the second list.
View 9 Replies View Relatedis there any rule for random number to be picked up? Any Rule, Any Formulae, Any Calculation Anything, Any authentic routine which may guess what would be the next random number? for example if first random number is 825587 then what would be the next?
View 4 Replies View RelatedI am starting a new Fantasy Football League. I am wanting to be able to enter players into Sheet 2 as they are picked. And I am wanting those picks to be simultaneously placed on Sheets 3-14 according to their position. Sheet 1 is a list of names according to positions. A more detailed explanantion is entered below.....
View 9 Replies View RelatedI have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData()
Dim counter As Integer
counter = Sheets("Imported Data").Range("Counter")
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("imported data").Select
Range("a1").Select
i = 1
Do Until i = counter
Range(Cells(i, 1).Value) = Cells(i, 2)
i = i + 1
Loop
Is there a way to make the contents of a particular cell in a worksheet show where to pick up another file. Here is my code to pick up a file:
View 9 Replies View RelatedI have inherited a spreadsheet that I need to update soon. It takes data that has a date field and creates a pivot table by month. The person who created it completed it thru February. Now I have added data for March and April, but the pivot tables can not find this new data and I suspect it is because of the date formatting (I have done the Change Data Source under PivotTable Options to include all the new data).
The reason I suspect the date format is that she built a button to run a macro named "dateformat", which is not in the workbook. I have tired to manually format the date using the format painter from the February dates that the pibvot table recognizes, but I still get no results.
A textbox on a userform is populated through cell references which have formulas. Works OK. I want to change this by getting rid of the formulas in the cells and have the textbox populated by a name. The following formula is entered in the "Refers To" box =DEGREES(ATAN(RADIANS(ATAN2(Sht1!$G$34-Sht1!$G$33,Sht1!$H$34-Sht1!$H$33)))*(LastDep-Sht1!$G$33))+Sht1!$H$33
The name is "TopLine" (without quotation marks) When I enter =TopLine anywhere in the worksheet, it gives me the proper answer. I have been trying to populate the textbox with "TopLine" but without success. I tried a listbox but that doesn't work either. I have exhausted the search possibilities (I think).
I have created a drop down list for items we quote on. each item has a different formula depending on the variables on sheet one Sheet1. I have listed the formulas in the column next to the drop down list for each item, but our now need the main quote to put in the formula in another cell when the item is chosen from the drop down list.
View 2 Replies View RelatedI have a userform that allows users to review, input and edit data on a spreadsheet. (thanks to Leith for getting me started on it a while back)
I want to be able to assign names to columns so that they can be rearanged in the worksheet without interfering with the VBA/macro/userform.
For instance currently the VBA for the row = "R" (equating it to whichever row the form is currently on)
I'm using MultiPage in a UserForm
I have multiple worksheets that can range from: HOLE 1 and go up to HOLE 30
I have other multiple worksheets that can range from: SAFETY 1 and go up to SAFETY 30
When I use the Code below it always goes to the 1st worksheet named either HOLE 1 or SAFETY 1
How can I have it so that when I click on the MultiPage Tab that it goes to the last sheet on the right for each?
Example: If I'm on SAFETY 5 it would go to that sheet, and not SAFETY 1 like it does now.
Here is my
Private Sub MultiPage1_Change()
Select Case MultiPage1.Value
Case 0 'page 1
Workbooks("Workbook.xls").Sheets _
("HOLE 1").Activate
Case 1 'page 2
Workbooks("Workbook.xls").Sheets _
("SAFETY 1").Activate
End Sub
I have this concept of a table of responses that have to be generated every month or on command. These reports export from a website into a certain format, its easy for me to adjust it so it can populate a the table. Although, i want to incorparate my date picker so everytime i pick a certain date a diffrent set of data will populate from the report. it seems very possible, just need to know where to go from. Either a VBA code or a fomula to allow for this to happen.
Heres and example..(cell linked to date picker)........column1 column2 column3,(date picker from microsoft),,each has a corresponding month
.....................................................................jan
.....................................................................feb
.....................................................................etc.
say i pick january, i want these columns to give me my data from a sheet or other workbook(the january report). i want everything linked so it will do this for virtually every month of the year. ive attached an example, the only thing i can think of is to make the cell that changes with when i pick a date to link to the report criteria.
See attcahed code. I need to add a line to the code so date picked by the user needs to be both weekday and not a day in the past.
e.i today : 20/06/2006
if user pick a date before today or a weekend date pivot table does not update.
Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Application. ScreenUpdating = False
If Weekday(MonthView1.Value, vbMonday) < 6 Then
With PivotTables("PivotTable1")
.PivotFields("DATE").CurrentPage = Format(MonthView1.Value, "dd/mm/yyyy")
End With
End If
Application.ScreenUpdating = True
End Sub
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
Excel 2007 and very new to VBA...
I have a userform (named "QAReviewForm") with a combobox (named "cboSupName"). I want it to populate with my named range "SupList".
FYI, the SupList is found on sheet 2, "Administrative Menu" in column E. E1 contains the heading "Supervisors", and my named range formula is
Formula:
[Code ] .....
When it runs, my form opens and the combobox is there, but nothing appears as options for me to select (yes, I have some values in column E).
I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies View RelatedI have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange.
I have another named range that actually refers to a range. Call it AllData.
Column A
Row2 56
Row3 44
Row4 65
AllData is a named range that refers to the range A2:A65536
AllData_UsedRange refers to A2:A4 by way of this formula.
=OFFSET(AllData,0,0,COUNTA(AllData))
How to I obtain an address of AllData_UsedRange in VBA code?
These do not work...
ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address
Evaluate(ThisWorkbook.Names("AllData_UsedRange"))
