I have an excel worksheet that consists of 4 tabs. Each tabs has approximately 60,000 lines of data. The data consists of user id's and menus that correspond to the user id. There are 131 user id's in total. I would like to be able to return all the menus for one of the user id's. If we need to start with one tab and work from there, that's fine.
View 9 RepliesI am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing.
So, If PRODUCT_A with OPTION_A, then PRICE = $X.
If PRODUCT_A with OPTION_B, then PRICE = $Y.
If PRODUCT_B with OPTION_C, then PRICE = $Z.
etc.
I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.
I am trying to lookup two distinct values in two columns (turquoise and green) in 'Cust data' tab and correlate them to the same values in two columns on 'Driver activity' tab, then return a result from column in yellow on 'Driver Activity' tab to populate the driver name in yellow column on 'Cust data' tab.
File is attached.
I have tried searching for this topic and have been unsuccessful. Perhaps my search was too broad. Either way, I am trying to understand how to search/query a specific data entry among several sheets, and then return corresponding values from adjacent cells.
View 3 Replies View RelatedI created a drop down menu from a named data range on a seperate spreadsheet in the same workbook. Based on the selection from that drop down menu I want a preassigned number to pull into another cell. I'm not thinking of the correct formula or something, because it's not working. Could it be because it's text? My spreadsheet with the data is as follows:
Bridger to American Fork 051725
Hunter to American Fork 051725
Hunter to Delta 051715
Hunter to Hiawatha 051728
Navajo to Am. Fork w/ front 051725
Navajo to Am. Fork w/o front 051725
The 1st column is the origination/destination and I named that range "freight hauls". Which is what the dropdown menu on another sheet (AP Reconcilliation) consists of. From that, I want the 2nd column number to pull into another cell on the AP Recon sheet. The formula I wrote that isn't working is: =index('AP Reconciliation'!A2:B8,match(A6,'Freight Hauls List'!B2:B7,0),2)
I have a production planning spreadsheet that is updated every week from a database to a new sheet. This sheet is 'Sheet1', last weeks update becomes 'Sheet2'. Every day people annotate and colour cells for various orders, depending on their place within the planning scheme. It's really only the formats that I need to copy across, I could use the VLOOKUP function for the values - So I suppose you could say I'm looking for a VLOOKUP macro/VBA Code which keeps the original formatting (cell colour etc.)
View 2 Replies View RelatedI have a list of people with SSN, about 3000. This list contains business done by each individual, which can be in all 50 states. I have another list of the same individuals with just their name and addresses and what we call "client number". I need to put the client number with the list for the individual states. See example list:
List of individuals with client numbers
123 John Doe
456 Jane Doe
List of individuals with state business
John Doe AR
John Doe CO
John Doe FL
John Doe MS
John Doe TX
Jane Doe MS
Jane Doe TX
Jane Doe AZ
I need for those client numbers to appear in the column before the names on list with states.
Example:
123 John Doe AR
123 John Doe CO
123 John Doe FL
456 Jane Doe MS
456 Jane Doe TX
621
532
249
I have these numbers located in column A (rows 1:3)
formula that will return my search result (in these case 532) as well as the information immediately above & below the location of cell that has my search info?
I am working on a spreadsheet for some packages we are sending to multiple recipients. I have figured out how to get all the weights calculated, now my Mailing Manager has asked me to come up with a calculation for the actual postage amount for all the 1400+ packages...Unfortunately it is not as easy as that would seem.
There are 9 different mailing zones and each zone has 19 weight categories and each category has a corresponding postage amount for the package. So I need to come up with a formula to calculate the package postage amount based on those multiple categories.
For example:
Zone 1:
0-1lb = $2.12
1.1-1.5lb = $2.12
1.6-2lb = $2.21
etc
How do I go about entering in that information from which a formula can determine the proper package postage cost and what formula do I need to use in conjunction.
This is all done within a address list so each package is a row and all weights and zones will be in a separate column.
Package is in zone X and weighs Y pounds therefore the postage is Z. Something like that.
I have four columns, A through D
Column C is returning a simple vlookup of A
I need Column D to return a value where C is TDMA return TDMA or when C is GSM lookup column B compare to tab2 (columns A through L) returning column 12.
I am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
So basically I am looking to build a formula that would take this information and return a text value.
if it could work anything like this sumproduct formula to bring back text that would be awesome:
=SUMPRODUCT(--(A1:A10="7654321"),--(B1:B10="ABCDE"),--(C1:C10="3"),D1:D10)
But instead of a formula that returns a sum of values, the return is a single value. The three criteria will always be unique. There will never be a case where there is a duplicate of all three.
I have below table and want to get new order quantity if the closing stock of a particular product is less than or equal to the ROL after viewing that a previous order of the same product has not been placed within the lead time of that particular product even the closing stock is less than ROL.
Product
Date
New Order
[Code]....
- 1st it match the product with the relevant one
- Then compare closing stock parameter
- Then finally look up the previous order and compare it with relevant lead time
I am attempting to get an INDEX function with multiple "IF" statements to return more than one result. Building multiple IF statements. That portion of the below formula works. However, I can only get the INDEX formula to return the first, single result.
Formula:
=IFERROR(INDEX(Func_Area,SMALL(IF(Const_Start<=DATE(YEAR($B$2),MONTH($B$2),DAY($B$2)), IF(Cost_End>=DATE(YEAR($C$2),MONTH($C$2),DAY($C$2)),ROW(Func_Area)-MIN(ROW(Func_Area))+1,"")),ROWS($A$4:A4)))," ")
Func_Area,Const_Start,Cost_End are all Named Ranges on Sheet "Proposed". My intention is to return a list of projects on Sheet Test1 for all projects starting construction after a certain date and completing construction before a certain date. I suspect something in my ROWS fuction or ROW-MIN+1 is wrong.
This formula returns the first result in the first row, but every subsequent row is blank...see attached spreadsheet.
I have been trying this for hours but to no avail.
I have a table with 4 columns headers
Name, Amount, Loc and Code
The name may look like ABC 1, ABC 2....
The Loc may be in US, GB...
and the Code may be AA, BB
I need to return a result "Y" if the sum of the amount is > 100
and "N" if the sum of the amount < 100 based on the conditions
of the following :if
1) Name is the same entity, such as ABC 1 and ABC 2 and
2) Loc is the same, US..and
3) Code is the same
I have attached a sample to illustrates the result
I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value.
I have a column between the years that calculates the percent of gross revenue for the specific department/cost.
I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever.
The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.
Sheet2:
col A = contains the style#
col B = contains the color of the style
col C = contains the size of the style
col D = contains the qty of the style,color, size
Sheet1:
I would like to do the following:
A1 = input the style #
B1 = input the color of that style
C1 = input the size of that style
then D1 should automatically contain the qty of the mentioned style, color, and size.
I am trying to write a formula to figure out Body Mass Indexes for certain age groups and whether or not they fall into a High or Low risk category. So, I am trying to write a formula that does the following. I have 3 columns, Gender, Age and BMI. I need the formula to do the following.
IF Gender = M AND Age >18, <39 AND BMI >7%, <19%, Then return an "L" into 4th column
IF Gender = M AND Age >18, <39 AND BMI >19%, Then return an "H" into 4th column
IF Gender = M AND Age >40, <59 AND BMI >14%, <23%, Then return an "L" into 4th column..............................
I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean.
What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E.
Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.
I have a spreadsheet similiar to the below:
COLUMN A.....COLUMN B
Person 1.......Process 1
Person 1.......Process 1
Person 1.......Process 2
Person 1.......Process 1
Person 1.......Process 2
Person 1.......Process 3
Person 2.......Process 1
Person 2.......Process 4
Person 2.......Process 1
and so on.
In the list, the "People" aren't neccessarily in order, for example, later in the list, Person 1 appears again. There are a total of 10 processes, and any person can do any number of each.
What I ideally want is in Columns D, E, and F, is something like the following:
COLUMN D.....COLUMN E....COLUMN F
Person 1...........3.............Process 1
Person 1...........2.............Process 2
Person 1...........1.............Process 3
Person 2...........2.............Process 1
so a summary of the main data, showing how many each process each person has got.
I have, and failed miserably, getting arrays to work but that might just be down to my own incompetence. I have researched into Countif's and Lookups, but it's not quite working.
The data in columns D, E, and F needs to be fairly compact so ideally I wouldnt want any spare rows between the end of the summary data about Person 1 and the start of Person's 2 details.
I want to look up a particular value and return according results horizontally and vertically. Attached is the excel.
View 10 Replies View RelatedHow to write formula of following condition:
Input date on left column and the cell on right will automatically look up this date from the list located in other spreadsheet and return multiple corresponding in sequence order? Once another date is entered on left column, it will do the same function again on right column?
I have been using this formula to do lookup and return values
"=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))"
I'm using this formula in a roster scene to pick up people that call in sick and display their restored job in a different cell. The problem that I am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another, then employee 2 calls in sick for the 1pm shift (the call was made after i have already restored employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.
Is there a way to stop it from changing the value once a value is entered in a corresponding cell?
Link to the original formula thread. [URL] ........
Is there a way to use a lookup formula to return all values of a given text, not just the value of the first listing of the text. In an attempt to clarify my already confusing request, if A5 on "worksheet1" is "Land" I want E5 on "worksheet1" to return the summation of values assigned to "Land" "worksheet2" "column B" any time "Land" appears on "Worksheet2" "Column A".
View 2 Replies View RelatedHere are two sheets:
Sheet1
systemip1 ip2 ip3 ip4 ip5 ip6
system11.1.1.11.1.1.21.1.1.31.1.1.41.1.1.51.1.1.6
system22.2.2.22.2.2.32.2.2.42.2.2.52.2.2.62.2.2.7
system33.3.3.13.3.3.23.3.3.33.3.3.43.3.3.53.3.3.6
Sheet2
ip system
1.1.1.3
2.2.2.3
3.3.3.6
3.3.3.1
Sheet 1 has 7 columns(system,ip1,ip2,ip3,ip4,ip5,ip6 and ip7)
Sheet 2 has 2 columns (ip,system)
I have to fill column "system" in sheet 2 with "system" listed in column 1 of Sheet1.
In other words look for "ip" in Sheet2 in 6 columns of Sheet1 and return column 1 of sheet1 as value.
I am trying to return a value from table below (column 4) based on values in the 1st 3 columns. I have data with the 1st 3 columns but need to lookup each and return the 4th so I have tried some formula's with if and vlookup statements but keep getting lost....
View 9 Replies View RelatedI've been working this for ever and can't seem to figure out the best way to go about it. I have attached an example sheet. All I need to do is figure out the Dept #... which is listed in Row 1, Column F:H. I want to match the project numbers and then return the AA, BB, or CC in Column B.
View 9 Replies View RelatedI am trying to make a search form in Excel 2003 where I can lookup data for a specific value which may have multiple rows. I have several columns, column A Tracking Number, B Order number, C Item Number, etc.
For example; Order number S6001 has three item numbers on rows 1,2,and 3.
I can get the data on row 1 using the basic "LOOKUP" function but not rows 2 and 3. How do I have all of the line items (rows) returned for that order number.
In Sheet1 are unique VM entries (Column A) which have a Group (Column B), Time (Column C) and a Reference (Column D) which can be same for other VM entries. In Sheet2 are the unique Reference Entries (Column A) which need Group (Column B) and Time (Column C) entries populated from Sheet 1. Multiple occureneces need to go in one cell, I realise this is not the best idea, but for this purpose they need to be and seperated by a comma or semi-colon.
In B2 I've used this formula:
=INDEX(Sheet1!A1:D11,MATCH(A2,Sheet1!D1:D11,0),2)
Which partly works, but only returns the first occurrence of H1 on Sheet1. I need all occurrences (i.e G1;G4;G8) returning for B2.
I can only think that some VBA script may be needed but I'm not really an expert in this area. In the full spreadsheet there are hundreds of rows and multiple entries, where Group and Time need to be populated on the second sheet
I am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.
For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.
I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],
SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]
='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")
I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.