Let's say for instance I have a vlookup that displays a the quantity of available items in stock pulled from a column in another workbook. Occasionally these numbers are negatives as they are backordered I would like to display these values as zero.
I have a MROUND function to round a cell value to multiples of 20 but to also return 20 if the value of the cell is less than 20. How do i combine the MROUND function with the if statement to give me that result?
I almost get my eyes killed whenever at the end of the month
A B C D
I have issued invoices with reimbursements to Mr. A so on 30th of the month, I issue a statement to summarize my invoices and reimbursements as above example. Note : An invoice and a reimbursement are always in a file(sheet1 and sheet2) and a solo reimbursement is a file(sheet1). "Invoice"(Folder) in drive D contains Invoice00100(SI11) & Reim00111(SE55).
Samples of invoice and reimbursement Invoice(sheet1)
A B C D E
Solo reimbursement is the same as above reimbursement table
code that can extract all excel file in "invoice" folder and auto create as my first above table. I would like to manual select the files which are taken to extract to the statement.
The formula I'm looking for would produce the following results: If City on Data matches the city on Lookup Table, and the name on data matches the name on Lookup Table, then bring back the code on Lookup Table.
Result would be: Results:CityNameResultsAtlantaDaveDAVNew YorkCharlieCHALos AngelesDanWill be blank, as the names on Data and LookUp Table don't matchSt. LouisDanDANMemphisStevenWill be blank, as the names on Data and LookUp Table don't matcm
In column A I have product codes some of which are repeated, in column B I have costs for the relevant product codes.
I want to vlookup the product code on another sheet, then Columns A & B picking up the cost in column B but if there are mutiple entries I want to pick up the highest cost out of all the entries with that product code.
I have an Excel workbook containing a number of worksheets. My problem is that on one sheet(BlkPick) I have Rows containing headings(Location Names)and a column containing Product names. I wish to lookup the corresponding value contained at the matching intersection on another worksheet. I have searched this forum along with google and found that INDEX & MATCH are what I should be combining with Vlookup and these work when used on the same worksheet but when I test using say MATCH alone I get an error when attempting to MATCH my result from a different worksheet.
I have a workbook with two sheets that I need to combine some of the data from. Sheet1 is a template to be used for translation purposes on Sheet2. I would like to put Sales Rep Info in column C on Sheet2 (has only Sales Rep ID) using Sheet1 as a translation table (shows Sales Rep ID and Sales Rep Info). I can do this with a vlookup function but would like to know how to do it in a macro so I can use on other more complex workbooks. Here is a sample data set.
What i want to do is to look up of the value of home and away games seperatly. if there is no match for the lookup i get the "N/A" and if there is no value i get "0". If i get 0 the formula will calculate as a lost game.
I have three columns of Vlookup data and three columns with a Vlookup formula. I need a fourth column to look at the three columns and see which ones are returning no value (cell with #N/A) in them and then return the cell that does have a value in them or the first row that has a value in it. The values are actually NAICS title descriptions and not numbers.
I need to lookup data from four different columns to match a value in another sheet. The lookup value has to start with "4" in order for it to be the correct match. I've tried the following formula for just two columns:
The problem I have is that the wildcard function isn't working in my If statement (apparentley that's how Excel was designed). The first vlookup returns the value "40042565" but Excel doesn't show it matching the criteria with the wildcard ("4*").
Any way to lookup the value in C2, search through multiple columns, and return the value that starts with "4"?
I want to add another vlookup criteria into the statement below and im having some difficulty, its currently has two called "Name" and "QrtName", i want to add another called "PlaceName". I have a named range called "Data" for the data the vlookup options use. It is working fine other only i want to add the third vlookup option:
In the example below I have created a validation list in B1 that references D2:D8. What I am then trying to do for cell B2 is create a vlookup and nested if statement that says if b1 is a number lookup the value in column D and return the value in colunm E and if b1 doesn't = a number, ie 'none' then return a null value. The same goes for B3 where if b1 is a number lookup the value in column D and return the value in columm F and if b1 doesn't = a number, ie 'none' then return a null value.dropdown list problem.
I have a list of parts that I have to create in a system. It is easier to do if I create a list of materials ahead of time of what components are needed. What I have done is create a part list that contains a list of parts, what the cost, whether they are manufactured or purchased, and what the sub component is if they are manufactured.
This worked great till I ran into my first sub component that contain its own sub components. I am attaching the spreadsheet so everyone can see what I am doing or more to the point trying to do. IF VBA is a better solution that would be great.
What I need is a formula (or VBA if it is better) that brings back the sub-components and their sub-components for each part until the parts tree for the original part number reaches the base purchased sub-parts.
I have the 1st worksheet set up in three different ways. The first one you run into is simply a length calculator and can be ignored.
The second way is the original way I had the spreadsheet working and it works fine for parts with only one sub-component. If a sub-part has more than one sub-component then it doesn't work.
The third section is where I started building what I thought would work, but it is clumsy and only will work nicely if I can place an IF statement that will only bring back a value for a sub-component if one exists otherwise it will remain blank.
What I would like is to have a formula that will auto fill the information IF and only if it exists for each sub-part listing all sub-components ( I need if it exists, cost, whether it is manufactured or purchased, any and all sub-components [followed by the same information as the parent] and vendor).
I am trying to use vlookup on the the income statements I am using. I have a workbook called overview with all the months and need the "total" of each month to appear automatically. The previous vlookup formula does not seem to be working.
I am trying to match up two sheets and I the match criteria is based on the data from columns B, C, D, E, and G. Sheet 1 contains ~20,000 line items of data. Sheet 2 contains ~250 line items. I am comparing the info in sheet2 vs. the data in sheet1. Both sheets are formatted the same.
For each row in sheet2, I need to compare against all rows in sheet1. For example; for the first row in sheet2, determine if there is a row in sheet1 that contains the exact same data for columns B, D, E, and G (all must be true) and the data in column C is False. Examples:
I am building a template for a report. The report resides on the first sheet of the workbook and the subsequent sheets is where the user would paste the appropriate reports so that the formulas on the first sheet can pull the appropriate data. I have all my formulas working fine with one exception.
The data in question needs to be pulled from the sheet "Paste Adcap Report Here" and the column is AA. The common piece of data between the two sheets is in column C of the Adcap sheet and column B of the main report sheet. What I need to do is conduct a vlookup between the report sheet and the Adcap sheet using the account id's and then return either the date value in row AA or the word ongoing which would be those accounts which do not have a preset end date. So far a co-worker had been able to come up with the following:
The code is a bit screwy as she had to leave early. The problem we encountered was the entry in column AA for a non-ending campaign. It is represented by two hypens (--). This is what tied her up. we could not figure out why the formula was returning an #n/a instead of the term "ongoing" when it encountered the (--). Again, if the formula pulls a (--) then the word "ongoing" needs to be displayed and if the formula pulls an actual date value then it needs to display this date.