We currently have a few hundred vendors we order from for a retail business. We receive price lists in excel format, but they all differ in how they are formatted, i.e. one may have a column header of "UPC" and the next may say "UPC ID" and they may be in different columns. We currently format a handfull of these price lists so they have the same headers so we can easily compare costs, margins, etc, but we don't have the resources to do many more than this.
I'm trying to set a formula that will calculate the price difference between two identical items in two different sheets. The items' order is not the same and each list includes some different items along the identical ones.
I have three cells, let's call them A1, A2, A3. Cell A2 contains a list of products. I need to be able to have the price for these products appear in cell A3. The prices vary based on the contents of cell A1. There is a different list of prices for each different value in A1. I've tried to figure out a way to use a lookup, but can't seem to wrap my brain around it.
Contents of A1 determines unit prices in A3. Contents of A2 is a list of products. Contents of A3 is pricing for list products in A2, but pricing is controlled by content of A1.
I have attached an example workbook to this message, with the sensitive data removed. I am trying to vlookup the "cube" for the product number, into the PO worksheet, IF the vendor numbers match.
Example: On row 2 of the "PO" worksheet, part number AC1000110, should have a cube of 2.5 for vendor # 11170. I'm trying to match the "Vendor" on the "PO" worksheet with the "Vendor" on the "Cube File" worksheet, and then return the corresponding "Cube", (in column F of the "Cube File"), in cell U2 of the "PO" sheet. So, what formula needs to be entered in cell U2?
I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.
Currently... B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y")) J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))
Example:
If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.
I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.
Hi, I've got a price list with 2500 different lines which the boss has decided need a picture against each one (the guys a legend!!). Can anyone help me with a macro that will look up the code in Column A, then add .jpg on the end, and insert a picture into Column J?
I have three drop-down boxes in 3 adjacent columns. Column 1 is free-choice, Columns 2 and 3 drop-downs are variable dependent on what is in Column 1. That works fine.
I have an issue with over-type but I can solve that with protection. Again fine.
BUT:
1. How do I make the user choose something i.e. not just leave the cell in column 2 or 3 blank by ignoring it (i.e. blank is an error but only after drop-down in column 1 is activated)
2. Also, if the user has completed the line (columns 1,2 and 3) and then changes column 1, columns 2 and 3 are now reading from the incorrect drop-down boxes (i.e. they are now in error but this is accepted and not flagged).
In the drop-down menu in col D I select the value “Egress”, then the drop-down list in col E is populated with the values of EgressCol. Now I select a value, lets say “Cash payment”. If at a later time I change the value of col D, lets say to “Ingress”, then “Cash payment” in col E doesn’t match the values available for IngressCol (the correspondent dynamic list for the Ingress value).
I need a conditional formatting formula that checks whether the value in col E is listed in the corresponding list of the value selected in col D. Request: I found this formula that works fine, but it gets Excel to work really slow.
On sheet 1 I enter data such as: Visit Date | Customer Name | Customer Number | Job Number | Product
On Sheet 2 i have a list of our 130 products which would be similar to '1130Tots Standard 24" ' I have made these as a list so we can choose from the list using data validation.
Now, out of the 130 products we have about 4 main different Catagories - Tots Direct, Tots RTS, Direct, RTS. - I could put what category they are in in a column next to the list on Sheet 2.
What i would like to happen is if e.g. a product is chosen from the drop down list, it could look in the cell in the column next to it and see what Catagory it is in and format the entire row on Sheet 1.
E.g. Direct changes colour to grey, Tots RTS to Pink, Tots Direct to Grey/Pink stripes, RTS stays standard no colour.
A bit of a development on a question i asked last week to cut out another stage. Currently people have to enter the product, then choose in the next column whether is is Direct, RTS, Tots Direct, Tots RTS then conditional formating will colour the rows. - Want to know if it is possible to do this all in 1 go by selecting the product from the list.
The analysis basically has 2 data components to it: The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.
The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.
What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:
1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)
2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least)
3) Pull the price for this "closest quantity"
I have uploaded a worksheet showing the structure of that data. [url]
Is there some VB code I need to do this, or can it just be a few simple formulas?
I need to find the lowest price, mark it up and designate which vendor it’s from so the CSR knows which one to quote back to the customer. Besides going through it line by line, is there a way to do this? It seems like there could be a formula where it pulls the lowest of the 3 prices and vendor name into a new “price” and “vendor” columns and then I can go through and mark it up.
Here’s a small sample of what the sheet looks like:
Is it possible to "Autocomplete" a Vendors name in an excel cell from an Access Database of vendors and then perform a vlookup from the database for contact name, address, phone, email, etc.... in adjacent cells?
For example if I begin typing "Acc" then I automatically get a list of vendors from my vendor database in Access beginning with Acc to choose from, such as Accent Cabinet, Access Grage Doors, etc...
I would like to create a range name when the vendor changes in column "A' and identify the range name as the name in "A".
Name Item QTY Bill A 2 Charley C 1 Charley d 6 Charley z 7 David x 6 David f 44
I would have range name Bill for B2:C2 Charley B3:c4. Since the data changes, I need to have the program run and delete old name and create a new range with new data.
Cell B23 contains an invoice base price in $'s formatted to 2 x decimal places Cell I4, I5,I6,I7,I8 all contain various percentage discounts formatted to 2 decimal places
I am trying to creat a formula in cell I23 that will subtract the discount of each of "I" range cells - lowest number to highest- in order. But each discount has to be subtracted from original invoice value in cell B23
I have tried using =B23*(1-I4)*(1-I5)*(1-I6)*(1-I7)*(1-I8) But this appears to be a cascading discount By my manual calculations answer should be $1261.60 But above calculation gives me $1289.94
Is it possible with 1 formula and avoid to create 300+ individual formulas.
The background info: each day we check the prices of bananas, sometimes we check 2, 3 or 4 times in the same day, and sometimes the price that is checked is the same, but most of the time the price is diferent throughout the day.
We need to know the average dayly price for a particular day, while taking in consideration that some days we have 3 diff prices, and some others we only have 2 or even just 1 price that was checked and thats the price for that day.
we have the following spreadsheet
Column A = Dates Dates when a price was checked, note here that there might be days of the year when in a single day the price is checked 2 or 3 times, and sometimes the price is different on the same day.
Column B = Price The price checked on the date indicated in column A
Column C = Daily Avg Price Date is just a date range from 08/1/11 to 07/31/12, a single day on each cell, that is intended to be the day reference for column D. C1 = 8/1/11, C2 = 8/2/11, C3= 8/3/11 and so on until 7/31/12
Column D = Daily Avg Price In this column we need to show the average daily price calculated from the prices and dates in columns A and B.
For example: when C1 equals 8/1/11, then D1 has to equal the average price for the product, taking in consideration the amount of prices annotated for that day in column A, with the prices of Column B for that day and providing the average price.
I could have had an easy =Average formula but, since sometimes we have 3 or 2 or 4 equal or different prices in one single day in column A, I dont have a constant for the data range for excel to calculate an average.
My only current solution is to go on each cell on column D and writing the average formula selecting the range that would apply for that average price. But that is taking so much time, and i have multiple other products and year ranges and markets that require this same answer.
I can only export vendor codes from the General Ledger rather than vendor names. If possible, I would like for excel to recognize the vendor code and assign the appropriate name. For example, HCC stands for Human Capital Consulting. The GL includes the vendor code and the voucher number. If I export the vendor code HCC/V0007 from the accounting system to cell A2 in excel, I would like for excel to pick up everything before the / (in this case HCC) and enter Human Capital Consulting in cell B2. I have a vendor list in excel that matches vendor code with vendor. I just need for cell B2 to pick up the vendor code from the list.
On an order form the customer will be able to enter the width and projection of several products that they want to order. The way these products are priced are in a Matrix of Width over projection. The current system i have designed is:
1. The pricing Matrix's have all been put into one big table and given a unique id per product matrix. 2. An advanced filter has then been run and and it extracts the appropriate matrix and copies it onto the process sheet. 3. An Index/Match formula is used to find the price for the inputted width and projection. It does this by finding the intersect point of the width and projection on the table.
Currently this will be put into a macro and assigned to a button.
The problem is that up to 15 (or possibly more) products need to be able to be ordered in one order form. With the current system it means there will be a lot of Advanced Filters and there will be a macro button that will need to be clicked after every product order (and they're could be 15 or more). Obviously this isn't very professional, it is time consuming and must be the hard way of doing it.
I was wondering what over systems that could be used for this sort of thing. The more solutions there are to this problem the better. Attached is the file. If you do post a solution it would be preferred if you could also post a file with a working example in. I find it much easier to learn the solution if i can see it working.
I am trying to create a price calculator for a particular product line which has multiple sizes and configurations. I would like to use drop down boxes so the user can select the size, type and attachments which will be narrowed down by the previous selections.
I would like the formula to be able to calculate the cost of the components based on the selections made. Based on some research I believe this can be achieved using data validation, indirect function and the vlookup function, however; I'm not quite sure how to go about this.
I have attached the document I have started for reference.
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)
Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines Sheet2 (has current material codes plus current pricing), has about 36000 lines
I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price?
I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances.
I would also like to reference the source on the next column.
For this ordering tool, I would like to calculate and present the grand total, starting from a fixed range of two columns (each 10 rows), where one has quantities, and the other has the price/unit. Since I'm not allowed to change the layout of the ordering sheet (we still fax orders...), I can not add a column for the subtotals. Also, not all rows are filled necessarily.
I had some success with this as a worksheet function: =IF(ISERROR(H30*I30);0;H30*I30)+..., but I would really like to have it as a part of a big VBA function, since colleages might alter/delete the worksheet formula.
I looked and messed around with For loops, but I can't make it work.
I need an all inclusive formula (all in one cell) that would look at the above table and depending upon the price paid (3000-14999 or 15000-99999 or 100000-249999) and depending upon what monthly term they choose (24, 30, or 36), the appropriate finance charge would be used to calculate a total cost (9-13%). The only way I know to do this is by using IF's and AND's, but there are simply too many arguments and I cannot properly write the formula.
I want to be able to use list as filters that will show me results for prices I need. I have no idea how to figure this out. I attached a sample file so you can see what I'm trying to do.
Suppose I'm a grocer and I use a spreadsheet to track my inventory. I sell fruits, vegetables, and dried spices. On one worksheet in my Excel file, I create the following lists:
FRUITS VEGETABLES Apple Potato Orange Celery
[Code]...
Then on a second worksheet, I have my actual inventory, which is this:
ITEM QNTY PRICE CLASSIFICATION
[Code]...
I need a formula for the "Classification" cells of the Inventory. For each "Classification" cell, I want the formula to do the following: Search for the corresponding "Item" in the "Fruits" list; if found, then put "FRUIT" in the Classification field.If not found, search for the corresponding "Item" in the "Vegetables" list; if found, then put "VEGETABLE" in the Classification field.If still not found, put "SPICES" in the Classification field.
So using my toy example:
The formula for the first row would search for "Banana" in the "Fruits" list. Because the desired text sting is found, the formula enters "FRUIT" into the Classification field.The formula for the next row would search for "Cucumber" in the "Fruits" list. It would fail to find it, so then it would next search the "Vegetables" list. Because the desired text sting is found there, the formula enters "VEGETABLE" into the Classification field.The formula for the next row would search for "Dried Oregano" in the "Fruits" list. It would fail to find it, so then it would next search the "Vegetables" list. Because the desired text sting is not found there, the formula enters "SPICES" into the Classification field.
If Excel can handle this level of automation, but I suspect there must be a way to do this.
On about 30 worksheets I have lists of names. These names are changed and added to now and then. I would like them to automatically populate a worksheet called "list". Preferably the names of every sheet with a blank cell at the bottom. The whole work book is about 60 worksheets.
I have a list of data that is currently separated into 5 different columns with a heading. Each item in the list is given a score 1, 2, 3, 4 or N, which is then placed into the appropriate column for that score.
What I want to do is to place the data in another sheet in the same workbook in a single column list separated by the labels from the columns in the first sheet, without any blank rows.
I am sure it would be easier if I could show you the spreadsheet and the result I want to achieve but I cannot see here how I can. I tried to copy and paste, but it only pasted as text.
