I have googled and found that you can add a slider within a userform, but how to set the values/parameters.
The aim is to have the slider max @ 5.00% with increments of 0.5% with starting value of 0.05%.
I have tried the properties section and it does not like any of my inputs.
This is a macro-enabled workbook for the Board of Education. The workbook is 31MB and located at [URL]. Click on View on the top and then look to the right on the ribbon. Click on the Parameter Selection icon.
Parameter Selection.jpg
This allows the user to select the entity to examine. If they do not select an ISD in combobox1, it should default to everything. If they select an ISD like Oakland Schools above, but nothing in Combobox2 than all School Districts will be included and so on. Once they have made their selection, they click OK. Now, what hopefully should happen is the data for their selection should be pulled from the “Combined” tab and put in the Pivot Tables on the Working Pivot Tables tab. Once that is done, another function should take the data from these Pivot tables and “refresh” the graphs and charts on the Summary tab at the front of the workbook.
One thing I’m still trying to correct is the data that populates this workbook comes from a web site that must be downloaded manually (don’t ask). It comes down as csv. You can see from the image above the “code” columns should be 5 characters with leading zeroes. Unfortunately, the source data is not that way. I have saved the source file as xlsm, formatted these code columns as custom since text won’t work and imported them into the workbook. The data moved over to the Pivot Tables (manually done to this point) does not maintain the leading zeroes.
I have a spreadsheet in which one column I'm trying to find a value in another table that has 3 columns (A, B, C). I need to find all the values in A, that fulfill the parameters of less than 1.5 in B, and great than 5% in C.
They all need to be listed in column H while skipping the records that do not meet this criteria.
I have a range of data in columns a-g (the length of which i am unsure of). I have been manually sorting them for the values in both column a and column g, but the data in column b-f are needed for my work. would it be possible in one sheet to have column a "y" and on another column a "N" sorted and then subsequently, the numbers in column g copied and pasted along in rows after so you would have a-g copied in h-n and then for the next number o-u and so on. there are multiple of the same value in column g, and a. would it be possible to make a macro which would sort this for me
View 1 Replies View Relatedslider.JPG
What is this and how do I create it?
I have been using a scroll bar in my project to select a value from 0 - 3
Is it possible to have a customised slider (whether a form control or something created from scratch) whereby the numbers 0 1 2 3 are displayed in a grey, red, yellow and green box respectively adjacent to eachother and a slider can be moved over the top to select a value?
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
View 4 Replies View RelatedI have one userform that loads combobox values upon userform Initialize. Though through a second userform changes can be made to anotherworkbook this workbook is saves any changes. when i close the second userform i need to rerun the 1st userform Initialize event to update the combobox's incase changes have been made.
View 5 Replies View RelatedI'm trying to do: Let's say I have a simple userform with labels as follows:
(Label1) John's birthday: (Label2) 12.10.1974
(Label3) Mary's birthday: (Label4) 15.03.1978
(Button1) Change Birthdays
Let's say want to change the birthdays I see, so I press the button and enter the new values through another userform with inputboxes.
How do I make that change permanent, so that next time I start the macro in the labels 2 and 4 I have the last entered values for the birthdays???
I have problems with my userform's listboxes. I have two listboxes, and I want second listbox's values to be dependent on first listbox's values.
And even more complicated, I need second listbox's values to be dependent on values on certain matrix.
In that matrix, row headings are listbox1's values and column headings are listbox2's values. How ever there are blanc cells on that matrix aswell. So if there is a blanc cell(s) on a row which (heading) is selected at listbox1, then I don't want that column (heading) which intersects with the blanc cell to be included to my listbox2 values.
Finally I want to insert the selected values from listboxes and the value from the intersection of those listbox values (headings) on that matrix to worksheet.
I included an attachment, where you can see my point better. However, as you can see, now the listbox values are not dependent on that matrix. Otherwise it is working like I want it to work.
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.
View 3 Replies View RelatedI am trying to add a 0 if it is missing at the start of the number in columns K2:K3000 and L2:L3000, The numbers do not have fixed lengths so I cant use the custom setting but if the number starts with a 7, 8 or 3 I want to add a 0
View 6 Replies View RelatedExcel Question 1.jpg
I need to have a code that looks up Column 1 (Parameter 1) first, then looks up Column 2 (Parameter 2), and inputs Column 3 (Value). The code needs to be in a SINGLE cel.
I thought initially using a Lookup function within another Lookup function might work, but I can't seem to get the code to work properly.
I'm working on an attendance tracker for church. We will have folks scan a QR code and fill out a 2 question survey: Name of person being checked in and date of check in. Both are mandatory and are set in such a way as to always have the same formatting every time. That's my first sheet, it just gathers the data. My second sheet actually has the attendance tracker with the roll and dated columns. What I'm trying to create on the second sheet is a formula that will search the first sheet for the date and name and return a 1 if they were there that day.
View 7 Replies View RelatedI have a datast that looks like:
ID Week Sales
1 1 $200
1 2 $300
1 3 $200
1 4 $250
1 5 $220
1 6 $100
2 1 $500
2 2 $100
2 3 $230
2 4 $250
2 5 $210
2 6 $110
I will have 3 parameters in different cells that will identify the range of data in which i'd like to get a SUM for:o
ID,
Week,
Number of weeks.
So for example my I would identify ID =1, Week = 3, # of Weeks = 2
I should get an output of: $450 ($200+$250, rows 3+4 from above).
I've tried using IF(AND and Sumifs nested.. but it's not working out..
I am using the following formula and I need to modify it a little:
=IF('Input Sheet'!A17="AR",0,'Input Sheet'!F17)
All I need to do to the above is add "MISC" to the formula. I have tried but I keep getting errors.
Basically I want:
=IF('Input Sheet'!A17="AR" OR "MISC",0,'Input Sheet'!F17)
Is there any way to use an Excel cell as a parameter for MS Query.
i.e. Select fields from table where field = "cell value"
I tried the $ ($D$5 ) got Invalid pseudocolumn error; which leads me to hope there might be a way to introduce a VALID pseudocolumn in my Query.
I have a query that has criteria based on "is one of". I would like to pass this off to a parameter selected from a list box where the user could select multiple items. Is something like this possible?
View 9 Replies View RelatedI need to modify the following formula,
=IF(G5=-105,-105,-G5-IF(ABS(G5)>251,40,IF(AND(ABS(G5)>221,ABS(G5)<251),30,
IF(AND(ABS(G5)>180,ABS(G5)<221),20,IF(AND(ABS(G5)>=100,ABS(G5)
[Code].....
I need to search for 3 parameters, L, M and P based on the *** and age from the attached excel file wtageinf.xls and enter them into the formula below
((X/M)**L) - 1
Z = -------------------------, L≠0
LS
or
Z = ln(X/M)/S ,L=0
where X is the weight of the child. The formula is for the Z score.
So below is an Sample (Actually about 200 people) of a manually finished product (without names). All parameters must be met.
Basically it will break the Big groups into smaller groups of 4. Assigning the smaller groups a numeric group number starting with 1.
Parameters. No more than 2 members of a club in one small group. No more than 7 lbs between lowest weight member and largest weight member. If it can not complete this, a 3 or 5 (preferably 5) man group can be made.
Sample 3 excel forum.xlsx
I am getting erors when i am trying to use excel parameters in sub query
select gen_fundnumber
from FilteredGen_fundmain
where gen_customeridnumber =
(select distinct gen_customeridnumber from FilteredGen_fundmain
where gen_fundnumber =? )
If I am changing the ? to '123' it works fine.. The second select with the ? parameter is running alone excellent.
How can I get at parameters passed on a command line to Excel 2007?
For example invoking Excel from Vista's Run command with:
"C:ProgramDataMicrosoftWindowsStart MenuProgramsMicrosoft OfficeMicrosoft Office Excel 2007" "C:GZNORPWeather StationParamTest1.xlsm" /e/abc/def
Should reveal the parameters "abc" and "def". But this does not happen!
Supposedly, the API function GetCommandLine does this, but I cannot seem to get it to work. Here's a sample of what I've been trying:
In order to be able to replicate something like the MATCH function, which accepts a String (or cell reference), an Array of Strings (or a range of cells) and then the match type as parameters, how do I declare such a function header?
it is returning #VALUE without executing any of the function. It is something to do with the Array of Strings that is causing the problem.
I've got some data formatted by column in the worksheet that I'd like to pass into the function and have it return something from an array. This is essentially a replacement for vlookup, but easier considering the volumn of data I've got. Take something like this...
Column N will have integers (0 through 20)
I want to pass contiguous parts of N into the function and have it return the corresponding sum of values from the respective positions in an array I defined locally. Here is the function:
I have a long list of past jobs, around 4000+. I have multiple fields, but I really wish to concentrate on the:
1) Job Type - Example would be Medical or Imprint
2) Job # - Correlates when it was done - Example would be 91059 would be a job within 2008-2009 fiscal year.
All the jobs are listed in the first spreadsheet. The second spreadsheet will hold generalized data broken down by the Type of Job and the fiscal year it was done.
I wish to first count how many jobs fit a specific job type. This was easily done:
=COUNTIF(Data!A2:A4121,A7)
A7 = Medical
A1:A4121 = the range of the names.
Count comes up as 346, which is correct.
I then want to add another parameter to break up the 346 by fiscal year. It ranges from 05-06 to 08-09. I came up with this:
=COUNTIF(Data!A2:A4121,A7)-COUNTIF(Data!F2:F4121,"9????")
Since the job # correlates to the fiscal year it was created, anything starting with 9 is a job done in 08-09. Anything starting with an 8 would be of course 07-08 range. I would have multiple fields with each different fiscal year.
However, when I put the 2nd formula in the function didn't work. It keeps the count at 346, which I know is wrong. I am not sure if I did something wrong here. Been looking at this for an hour and can't figure out what is wrong. The jobs that are medical within 08-09 fiscal year should be 120, but it keeps at 346.
Im currently using the below formula but was wondering if its possible to combine the parameteres that im looking up in coulmn L in sheet one so i dont have to do the formula three times? something along the lines of={"P11111";"P22222";"P33333"}?
=SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P11111"))+SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P22222"))+SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P33333"))
I am trying to write an SQL query using parameters in an Excel Worksheet and then return the results to the current sheet ar cell B11. I have found it imposible to make it work in SQL so am trying to run it from VBA to pass the parameters. These are named cells StartDate and EndDate in cells A2 & A3 respectively and should replace the constant dates below. I am working in Excel2003 & MS SQL 2012.
I have tested the query on SQL server with fixed values (which are still in it). It works fine & I have pasted it in.
I took an existing query I found & tried to modify it for Excel2003 but ... Whatever syntax I use, I get a Run-time error 424 when I get to a line with 'connection'
Code:
Sub DateQuery()
With ActiveWorksheet.connections("ndserver01").oledbconnection
' .Connection = "OLEDB; DSN=Excel Files; provider=SQLOLEDB; Catalog=NDM_Sage200;Data Source=ndserver01"
.Refresh BackgroundQuery:=True
.CommandType = xlCmdSql
[Code]...
What have I done wrong
I have some code that works fine below. It deletes an entire row based on the value in column J.
Ideally I would like to give the user the option to choose which column to base the deletion on. i.e. some kind of drop down box or input parameter where they could choose a different column "A", "B", "C" etc...
Private Sub CommandButton1_Click()
'Removes values less than 0
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("J1:J1000"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value)
I'm trying to figure out how to pass parameters to a date field in a macro to control the display in a pivot table.
The macro currently uses a With statement to set the properties of 177 of the 180 dates to false, leaving the remaining three dates true and visible.
