see 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".
How to get the value 0 if the formula can't find the specific parameters given below. What is the extra bit I need to add to this formula below?
=INDEX(Bayswater!1:65536,MATCH("Sim Only PAYM",Bayswater!A:A,0),4)
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 RelatedSomething 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 creating a user defined function in excel VBA. What it is supposed to do is predict dates. The sheet has frequency of events per year (max 4) and last event date. I am thinking to have parameters: frequency, last date, q2date, q3date, q4date...
so basically i want to type the function name (PREDICT) in the q1date column and have the function generate data in the q2date, q3date and q4date columns by using the cell reference in the parameters.... is that possible? I searched the net for solutions, i came across a 'ByRef' command but with no success to my function
i need to essentially find and replace udf's and the complication is rearranging the parameters in the udf's into a new formula structure.
so my question, is there an easy way to extract a parameter (or all parameters) from a function?
for example, if you had = SUM(A1,A2,A3:A6) you would return 'A1' as the first parameter, 'A2' as the second and 'A3:A6' as the third.
Obviously it gets complicated when you nest functions such as =SUM(IF(A2=1,0,A1),A3) etc and the if() function would be parameter 1.
Is it possible to use cell references to define parameters in an SQL query to an MS Access database?
I assumed it was similar to using SQL but I'm getting an error in excel.
Here is my query: .....
The error I get in excel is: "Invalid character value for cast specification" and it wont let me specify a cell.
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
multiple search match and replace content in a different column so for example
new workbook (look up table)
sku
search1
search2
[Code]....
so something like where you compare two tables and find and replace based on another cell that matches in my sku.. more details would be if the table column aren't exactly matching but the column header and the row header would match and fill or replace in the correct/corresponding cell is there a macro or vba to do this job in excel?
This time I'm trying to use the max value and row value at the same time, but it won't let me. Here's what I'm trying to do...
A B C D
1 a b c d
2 7 9 4 2
3 e f g h
4 8 2 7 8
5 i j k l
6 4 3 3 9
I want Cell A7 to return the letter before the highest value in column A...
A7=e
B7=b
C7=g
D7=l
I tried to do something using two cells, but even then I couldn't figure it out.
I thought a ROW(MAX(A1:A6)) would give me the row and I could go from there, but that didn't work.
I have a spreadsheet which has about 100 different SKUs in column A and the inventory for each SKU by period in columns B:Z
So for example B would be P1W1 and C would be P2W2, etc
what kind of lookup function would I use in VBA to find per say, the value of SKU #: "27017" in P1W2 ?
Apart from pivot table.
I have a sheet that has six sets of three columns on it. The columns are labels Ball #1, Number Times Drawn, Last Date Came Up. The first five "sets" are the same except the ball # changes from 1 to 2 to 3 to 4 to 5. The last set has columns labeled Mega Ball, Number Times Drawn, Date Last Came Up. Yes I am doing stats analysis on lottery numbers for a project.
What I want to find is the last date drawn for each of the numbers, 1-56, and have it go into the third column of the 3 column sets.
I will use the first column set for example. I have numbers down the first column A numbered from 1 to 56 (In series). In the next column B, I have the number of times drawn for each number, which I have working. In the last column of the set I want to have the Last Date Drawn.
I tried the following function (Formula):
=INDEX(Table1[Date],SUMPRODUCT(MAX((Item=A2)*ROW(Item)))-ROW(Table1[Date])+1)
This kind of worked. It returned a date but it was not correct. It actually said the same date for each number, which I know is wrong. I can't figure out where it is actually pulling that date from.
I should clarify that the drawing numbers are on a second sheet labeled "Mega_Millions_Numbers" in a table named Table1 and the Date column is A with a column label of Date. The table has column headings of Date, Ball#1, Ball#2, etc. through to Mega Ball column.
What I need is a function or formula to pull the last date drawn. I am NOT good in any way shape or form with VBA so if I can do this without VBA it would be best for me. I can follow along with formulas and functions well enough, just not VBA.
I will need to have this formula copied down 56 times per set and there are 5 sets so i will have to modify it for the other columns. (If your keeping track that totals 336 formulas).
In Sheet1 I've got a list of names in column A, then I've got a worksheet for each name in the list. I'd like to fill in column B with information from the worksheet of the same name as in column A.
Quick example...name in column A is Davis, want to fill in column B with a value from cell C3 on worksheet named Davis and do it for the entire list. Data being looked for is in same cell on each worksheet. Tried using a formula and dragging it down the list, problem with that is it doesn't change the sheetname just the cell.
I have 2 sheets in same excel.
Sheet 1:
A B C D E F
HR 2015 Q1 Database Planned 176
CRM 2014 Q4 Major Product Planned 125
HCM 2015 Q1 Database Planned 113
Sheet 2:
A B C D E
Doc 2015 Y 176 -----
simple requirement is to search column A in sheet 1 based on Column D in sheet 2. and write it in Column E.
The answer should be
A B C D E
Doc 2015 Y 176 HR
I need to perform this search on various fields in sheet A. this is a small snapshot of sheet. Vlookup wont work in this condition as there are several searches I need to do, I cant change the columns.
How to find the column based on my active cell...if my active cell is in A1 then it pops up saying your in column A.
View 3 Replies View RelatedFirst one needs to look through a single row and find where the first cell with a value (text) in it, then finds the date that that cell is under.
Second one needs to look through the same row to find the last cell that has a value (text), then finds the date again that that cell is under.
Example workbook is attached
i want to search OK7V0T in all cells and return the cell reference beside matching data row
serial 1serial 2serial 3
box-4OK4KCUOK4KI0 OK4LLS
box-4OK60ZDOK62AP OK62QU
box-4OK6TA5OK6TBP OK6TFW
ABCDE
FGHIJK
If the above is in different cells, I want to be able to move the data in "G" to the cell beside "E" (up a row and over 4 columns) IF the cell to the left of "G" equals what I specify is in "F" (for example use "product"). I would want to apply this to an entire sheet, not just once. If the macro (not sure what its called) could also then delete the row that contained "G" after it moves it, that would be even better! The output in the example above would be:
ABCDEG
F HIJK
I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.
View 3 Replies View RelatedI am making a form with drop down boxes and auto fill to make things easier. I have one Box for the Company selection another box for the occupation selection and I need a formula to find a $ value based on the selections made in these two cells. If I could establish the actual cell reference of the selected data in the second drop down list. The data will be much larger than this example and will live on a separate sheet.
View 2 Replies View RelatedI have two spreadsheets.
spreadsheet 1:
Lookup from Order numbers listed from A5:A177.
requested formula in I5: I would like a lookup to sheet 2 based on the order number (F19:F191), to return the cell above the first non-blank value.
spreadsheet 2:
Lookup value:Order number listed from F19:F191.
Data search:AY19:CI191
return the (date) which is in the range above the data search from row AY18:CI18.
I've had a look at few forums but i'm getting mixed responses, having to use index / match / lookup / min / --.
I want to enter a unique ID into an input box which will tell which row that id is available and then it will again ask me which column the cell needs to be highlighted. Once i enter the data, it will then ask me what is the change in data (again via input box). i can then enter the change data and then it stops.
View 1 Replies View RelatedNeed the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
