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 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 Replies View RelatedI 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.
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 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 am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies View RelatedI would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
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.
See the attached file. I think index and match may help me having done a search several times in these forums, but having been trying to do this for several hours I am just struggling to understand each part of the formula unfortunately. I have a list of data (cellsA1 to C33) which I need to use as my source for the information that is displayed in cell I2 based on the two selections made in cells F2 and G2.
I can't use filter and data sort in this situation - I am creating a spreadsheet for someone who truly knows nothing about excel, so I need this to be as simple as pointing and clicking at cells F2 and G2 and the formula doing all other work. Is it possible to do what I am asking for (hopefully the fact that peoples' names are repeated and months are repeated won't cause a problem?
if Cell F42 contains 50%, then my fomula returns "wrong"
=IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong")))))
how do I get the formula calculate correctly?
I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?
=IF(SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B))=0;"not found";INDEX('sheet 2'!D:D;SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B));1))
(I'm working with the dutch excel version so it might be that ";" must be ",")
How can I get a vertical lookup or sumIF formula to check multiple tabs for a given value?
Or - is there a way to specify the tab? For instance, put "Tab A" or "Tab B" in Cell A1, and have the lookup formula reference the value of Cell A1.
I'm trying to sum a criteria of all M's in one column that are x's in a different column, throughout multiple worksheets.
I'm able to get the summary number for 1 worksheet using the below formula (*W1 is the worksheet name); however, how do i encapsulate all the worksheets (lets say W1 through W10), please note that some of the worksheets have different ranges (meaning, not all are from Row 2 to 6)
=SUMPRODUCT(--(INDIRECT("'W1'!C2:C6")="M"),--(INDIRECT("'W2'!D2:D6")="x"))
I tried to replace W1 with W1:W10.
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've got a matrix combining actions on the left hand side (Col A) and owners on Cols B to F. I have drop down list for each action/owner combo: N/A, OK, KO, TBD.
Owner #1
Owner #2
Owner #3
Owner #4
Owner #5
Results
Action #1
OK
TBD
N/A
N/A
N/A
OK
[code]....
I would like to create a formula in Col G that does the following:If any of the values in columns B to F equals OK, then OKIf any of the values in columns B to F equals KO, then KO I've been tinkering with multiple IFs and quite rightly got bogged down as too many criteria to take into account...
I am trying to review a cell range for a specific criteria, and then sum up another cell range if the criteria matches. Here are the formulas I have typed in - there are two columns I am trying to calculate using the same formula, they are next to each other:
=SUMIF('MASTER POINT SCHEDULE'!I2:I841,"0ACA101",'MASTER POINT SCHEDULE'!O2:O841)
=SUMIF('MASTER POINT SCHEDULE'!J2:J841,"0ACA101",'MASTER POINT SCHEDULE'!P2:P841)
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 a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
View 3 Replies View RelatedI recently learned how to count cells in a range based on the value from another column (excel 2007) How to count cells in a range based on the value from another column but now how I can go about returning a value from another column that matches the conditional counting. For example in the table below I'm first wanting to find the rows matching "chr15" from column A that also have a value from column B that is greater than 25,000 and less than 3,000,000. But what it I wanted to instead report the corresponding values in column C? I've played with VLOOKUP to no avail but I'm not sure if that's the right line of thinking. The answer would be the values in bold.
A B C
1 chr2 12008 AA
2 chr2 149700 BB
3 chr15 51 CC
4 chr15 5624 DD
[Code] .........
I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient.
For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..
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.
Spreadsheet 'Raw Table' has many, many rows, and a dozen columns of raw input data. Each row has date, number, and text fields.
Spreadsheet 'Dashboard' is a somewhat simple one page summary of one day's worth of data entry from Raw Table. Dashboard isn't a simple table, but more form-like in appearance (not sure if that matters). Essentially, the values from Raw Table are spread out in different locations in Dashboard, and not just in a row.
The Dashboard fields gets their values from Raw Table based multiple criteria, the most important being a manually inputted date entry at the top of the Dashboard. The other criteria are simple numbers (ie, 1, 2, 3, 4....) built into the formula. So a set of criteria for one particular cell in the dashboard could be: 4/6/2014, 1, 3. Those 3 criteria are unique identifiers: there is only one match, ever. If those criteria are met, then a value from a specific spot in that row from Raw Data is placed in the dashboard field.
Here is one formula example for one such field in Dashboard:
=INDEX(('RAW DATA'!$D$1:$D$20000),SUMPRODUCT(('RAW DATA'!$A$1:$A$20000=$D$1)*('RAW DATA'!$B$1:$B$20000=1)*('RAW DATA'!$C$1:$C$20000=3)*ROW('RAW DATA'!$D$1:$D$20000)))
The first sumproduct criteria is a match for the date in Raw Data's column A with the manually inputted date in Dashboard located at D1.
The second criteria is match for the value in column B with a value of 1. The third criteria is match for the value in column C with a value of 3. (Again, this is an example. The 1,3 set can be any specific combo from 1,1 to 8,8. Thus, a day's data can have no more than 64 records of a dozen date, number and text fields.)
The Index function is obviously array,row,column. So this example index function results in the contents of column D in the row that the sumproduct function generates.
This dashboard spreadsheet works perfectly everywhere except four fields (out of 100+ instances of the same formula). In the faulty spots, the value that the index function is returning in Dashboard is basically the contents of column D in Raw Table in the row that corresponds to the place the formula is located in Dashboard. It's almost like there's an error in the formula result, and the index is defaulting to the row number in Dashboard. But I've tested this 8 ways til Sunday. I can even straight-up copy these four faulty fields to another part of the dashboard, and the results are magically correct. And I've checked to see that the actual sumproduct function result is returning the correct row for these fields. I don't have an answer as to why it's defaulting to the formula row number in just these four locations, and not the row value that the sumproduct function is correctly producing.
I've thought maybe it was a data type problem in Raw Data, but that route produced a dead end. It has to be something with the Dashboard. I'm going to start over with a new Dashboard spreadsheet, and see what happens. One thing I did was, early in development of the dashboard, use ctrl-shift-enter in the first faulty field, then went back and changed the formula wholesale to something different, and copied that cell to (at least I think) the three other faulty spots. But if I went back and completely changed the formulas and did away with the brackets, the cells should be fresh, no?
