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.
I have a pivot table that has several row labels and 1 column label. I am trying to create a Ranking table outside of the pivot table because I am using Excel 2007 and can't use the new Rank option in Excel 2010. My difficulty is in being able to rank each subgroup of the row labels.
I have thrown a column for "Target Rank" in the data, just to validate my calculation in the PivotTable worksheet, but it is only correct as long as all Company and Product Name options are selected for a given Sex, Age, and Scen Defn.
My non-dynamic formula is:
=RANK(GETPIVOTDATA("Target",$G$5,"Company","AIG a","Product Name","Elite Index UL a","Sex","F","Age",25,"Class","1-Best NS","Scen Defn","Overfunded A"),$L$8:$L$30).
I have figured out how to make some of the items variable to this degree:
=RANK(GETPIVOTDATA("Target",$G$5,"Company",$I8,"Product Name",$J8,"Sex","F","Age",25,"Class","1-BestNS","Scen Defn",$K8),$L$8:$L$30).
I know can make the $L$8:$L$30 copyable across columns by changing it to L$8:L$30.
My dilemma is several fold.
1. If I filter the Company or Product Name the $30 in the range no longer applies. Is there a way to make the bottom of each grouping dynamic?
2. Is there a way to make the item entry associated with the "Class" column label a variable so I can copy across columns?
3. Similar questions related to the row labels for Sex and Age?
4. Lastly, I can write an expression that would make my Rank columns blank if the Company column was blank, but is there a way to reset the data range to look at the next block? That is, if "Age" changed (was not blank) that would indicate the top of a block of data.
I have tried to think of ways to use INDIRECT and I don't know VBA well enough to do it in a macro.
ClassValues1 - Best NS4-Nonsmoker1-Best NS4-NonsmokerTarget RankAV 5 RankAV 10 RankTarget RankAV 5 RankAV 10 RankSexAgeCompanyProduct NameScen DefnTarget Target RankAV 5AV 10Target206420125F25AIG aElite Index UL aOverfunded A 1,520 20 12,848 33,607 2,135 9181961618Allianz LPLife Pro+Overfunded A 2,796 9 10,613 26,147 2,847 7221892217American GeneralElite Global PlusOverfunded A 2,810 7 7,930 26,323 2,810 1913617198AmericanGeneralChoice Index GULOverfunded A 1,610 19 11,388 31,409 2,205
[Code]...
I have a workbook that contains two sheets with two separate queries. On the first sheet I have two cells designated to accept the values for the parameters. The first sheet accepts the values and populates the table correctly. The second sheet doesn't seem to accept the values and just returns blank rows.
View 9 Replies View RelatedI am attempting to create a template that will utilize data from an Access table, and allow the user to select the parameters in the Excel file. The amount of records in this table is slowing down the Excel file, and I would like to lessen the amount of data that is imported based on the user selection.
The Access table will have three columns:
Part Number|Line|Catalog Code
I would like to import all three columns, but allow the user to select the records imported based on the catalog code.
The users do not have read or write access to the sql tables, so I run an Access query at the end of each day to create an Access table that they can access.
I have 2007 versions.
I need to combine AHK and Excel, and in this I came up with the solution to save my parameters in a file, and let the workbook autoload it when I open the workbook. To do this I need to pass a command line parameter, or working directory to the workbook, as I will be using many different work directories for this to make sense.
This is only a matter of how to execute my workbook so that the path is included (and usable) to the working directory
I have tried
..workbook-name.xlsm /p "workdir"
But it results in a "grey" version of Excel aka. Excel is open, but no workbooks are.
I have also tried
excel.exe workbook-name.xlsm /p "workdir"
But this did not work either, and is not an option as we use both 2007 and 2010 at my workplace where this is intended to be used.
I can do it very easily by using absolute paths in the Excel auto-load, but as I have explained already this is just not an option.
I would like to develop a user-friendly Excel 2010 spreadsheet that would allow the user to enter from 1 to 10 (i just picked this number at random just to have an upper limit) employee numbers in cells A1-A10 and "click" on the VBA Code button that would invoke an Access Query and in the process return all the applicable data gathered from the query back into the Excel Spreadsheet. Believe it or not, I can do all this mentioned so far. BUT what i cant do is my second option for the user which is allow them to pull ALL of the employees back into the spreadsheet using the same query.
In summary, using Excel as a frontend dashboard, I know how to get a specific number of employees' information from a query and I know how to get all employees' information from a query but I dont know how to get either/or. In other words, I could do this with two queries and two "VBA-Code" buttons but I would like to do it with one query and one button.
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 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.
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.
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
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 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 RelatedI'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.
I have a workbook that requires a multiple of 3 to be entered in a cell. If a quantity other than a multiple of 3 is entered, a Msgbox pops up and requires the quantity to be changed to such value.
I am looking for where this is handled. I found part of it in a Private Sub:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
But I cannot locate where the other parts of the code is stored....
I would like to see how this is handled so I can incoporate this in my own worksheets.
The web site is http://scores.covers.com/college-foo...chups.aspx?t=0. This website defaults to a Top 25 scoreboard. I would like to access the Full scoreboard instead. I've looked inside the web site source code for the parameter names, but using the most likely candidates in web queries pull in the Top 25 scoreboard instead. The web site name does not change when scoreboards are changed.
View 9 Replies View RelatedI need to assign a procedure to the OnKey method, but VBA does not let me specify arguments with it.
View 4 Replies View Related