Excel 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 Relatedsee attachment.
A2 is the given length and B2 is the given weight. I want B14 to net the answer of "Type G" because the length (A2) is equal to C10:C12 but the weight (B2) is within D11 and E11.
How do I formulate B14 to net the answer of "Type G"? This has to be flexible as the data in A2 and B2 will be entered in differently each time and B14 needs to find the appropriate "Type".
Something has X views, 10,000 for this example. I want:
1) The first 100 views to be worth 3 points each (=300 points)
2) The second 100 views to be worth 2 points each (=200 points)
3) The third 100 views to be worth 1 point each (=100 points)
4) Any view therafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.
How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)
How to autopopulate numbers from data sheet based on Selected month.
View 4 Replies View Related1) I have an invoice form, and need to create a formula that will allow me to calculate a discount IF an item number begins with "C" or "CE".
Example: Item # is in cell F12. (may or may not begin with "C" or "CE")
Item price is in cell J12.
Extended price (qty x price) is in cell K12.
In L12, I need to calculate a discount (from % in fixed cell L9) on the figure in K12 - based on whether or not the Item # in F12 begins with a C or CE.
If it does not begin with C or CE, then L12 needs to equal 100% of K12.
(The 2 parameters I referred to in the title were:
C, followed by a number
CE followed by a number )
2) The biggest part of the dilemma is that the person using this spreadsheet is totally unfamiliar with Excel formulas, spreadsheets in general, and has to send this finished product to a client each week. So I need this to be as simple as possible - which seems to me to be a formula (that can be copied to insert rows, etc. if necessary).
I get about 10 text files at the beginning of each month that I need to import into Excel. The file layout does change slightly from month to month so I have an Excel spreadsheet with the layout as the first worksheet. Each text file is in fixed layout form rather than delimited. The structure of that page is that the name of the first text file is in cell A1. The cumulative field widths are below that and the data formats are next to the field widths (in the adjacent column “B”). T=Text, G=General, D= Date with 3 flavors of Date: YMD, DMY and MDY. For the second text file, it repeats the structure in columns “C” and “D”, the third text file in columns “E” and “F” etc. What I would like is for the macro to open the first text file (based on the name in cell A1) and import it into a new worksheet (same file) using the cumulative field widths in A2 through A8 and the formats from B2 through B8, then import the next text file to a new worksheet (same file) using the cumulative field widths in C2 through C8 and the formats from D2 through D8 and keep going until all the files have been imported. The sheet names should be the same as the file that is imported (the “.txt” part of the file name isn’t important). I’ve attached a copy of the Excel spreadsheet that has the file names, field widths (cumulative) and field formats and a 2 row sample of the text file for the first import.
View 6 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 want to create an Excel sheet that calculates the charges of a freight based on predefined parameters.
I got 3 service types as shown below, two freight types and 12 different destinations both NT and SA have 6 different destinations:
I want Excel to do the following:
If Road and Carton and to NT-Country and weight is 7 then 7*1.000089 is the result
1 to 15
>15
Service
Type
NT
METRO
0.337284
0.337284
[Code] .......
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 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
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 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.