I'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?
View 2 RepliesI am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace PRODUCT with a SUM.
I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.
So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.
The drop down list displays the products and can be added to by typing new product names on the other sheet.
What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.
I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.
View 4 Replies View Relatedi am currently working on cost analysis of large projects.
I have a worksheet with about 100 headings and i would like to sum up the totals of 'quantity x unit price' for each product.
I tried the sumproduct function but it did not work and i had to do the '=sum(b4*c4,d4*e4,f4*g4,..........) to get the result in the total column. I attach a small sample of the file.
I have a worksheet that contains data on products including prices and I want to use that worksheet to fill out an invoice for every sale of those products. I know how to make the list/table dynamic and varify the data to "list", then use VLOOKUP to get the price to appear, but what I'd like to know is how to overcome the problem that will arise when a product's price has to change. Eg. if on 1/1/10 the price of product A rises from £15 to £20, how do I ensure that 2009 invoices do not change when I update/edit the price, but that the current price goes up? So far, the only thing I can think of doing is to create another product, but there are several other bits of data about each product on the worksheet which will not change (weight, height, width, supplier etc), so I don't really want to have to duplicate the unchanging bits and also leaving the productA with the old price in the list may lead to accidentally selecting the old price. What do you think? Is there a way of maybe fixing the price to date or something?
View 9 Replies View RelatedOn 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 need to look up a product based on the number and the qty. see the example attached.
On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.
I'm trying to set a cell on one sheet to be equal to the product of two cells on another sheet. The problem is that one of the cells on the other page is dependent on the variable T. This is what I've got and it's giving me errors
Worksheets("Output").Range("K14").Formula = "Worksheets("Calcs").Range("D17")*worksheets("Calcs").Range("D17").Offset(10+T,0)"
When I record a macro it gives me this, but again, I need the last cell in terms of "T"
ActiveCell.FormulaR1C1 = "=Calcs!R[3]C[-7]*Calcs!R[14]C[-7]"
In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.
View 2 Replies View RelatedI want to calulate a cell to show if a a product is in warranty or out of warranty from a date in another cell.
=IF (D25<365, "In Warranty","Non Warranty")
I think I may have got the syntax wrong.
i would like to create a userform with a textbox to enter a product code , a command button to run VBA and a big text box to show output of VBA from Textbox 1 which i will connect to DB and pull back information relevant to that product code , is this possible i can create the userform no problem and already have code to pull back from DB into Excel ,
View 9 Replies View RelatedI want to enter the word 'Closed' in cell B1 when the due date in cell A1 is exceeded by 272 days. I have tried using conditional formatting and excel accepts the formula but nothing seems to happen. Here is what I have tried;
B1=IF(A1<=NOW()-272,"Closed")
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
Automatically copy formula to next cell when i enter something in perticular cell ....
View 11 Replies View RelatedI am trying to enter a value in a cell which is dependent on a corresponding value in a table. I have a table A which has a cab type and a corresponding cab number.
What I want to achieve is that when I enter the cab number in another table say in cell (D3), the cab type is automatically entered in cell (C3).
Table A
Cab Type
Cab No
Indigo
100
Indigo
758
Innova
1544
Indica
1675
I am trying to enter a value in a cell which is dependent on a corresponding value in a table. I have a table A which has a cab type and a corresponding cab number. What I want to achieve is that when I enter the cab number in another table say in cell (D3), the cab type is automatically entered in cell (C3).
Table A
Cab Type
Cab No
[Code].....
I am trying to get the Price to automatically fill cell C11 of the attached spreadsheet when a user enters Grade and Thickness information. I have tried index function, match function, vlookup function, and combinations of all with no luck.
View 5 Replies View RelatedI am trying to organize it in a way that I can have all my purchases added based on my text criteria of certain purchase... Ill try to be as clear as possible. I have already organized it in a way that when I paste new months statement in my Excel file it sorts it by date, newest on the top, and I made Conditional Formatting by color on column D which states the Description of purchase. Starbucks appears in description cell but it is followed by number and location making each Starbucks cell in column D different in name. So its hard to use Exact phrase formula to combine the amount for that purchase.
My question is: Is there a formula that find a PORTION of text in a D column and automatically adds the price to the right of it together in one cell. There is a formula that looks for the whole text in the cell but that doesn't work when my name has always some extra numbers at the end of the word Starbucks
I own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.
Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.
All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
I would like to place a value of like say 100 dollars if the cell next to it is populated with any text for example: customer then 100.00 so if I type anything the cell, the next cell populates a specified value
View 5 Replies View RelatedIn column B I will be entering a number and I would like it to automatically enter gallons in column c when i do so. So if i enter 356 in b2 i want it to say gallons in c2.
View 1 Replies View RelatedSee attached sample sheet for more detail. I used code for show date and user id in cell H and I, but my problem is when ever I write some thing between cells A to G and enter the pointer automatically jumped to cell H, I want when I enter the pointer should be stay next cell.
View 2 Replies View RelatedI am working on blood pressures. I want cell C1 to place either, "Prehypertension","Stage I Hypertension" or, "Stage II Hypertension" depending on the values of cells A1 or B1 ....
View 7 Replies View Relatedwe have a Excel 2010 worksheet containing multiple tables.
Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10
Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17
On a separate worksheet we want to calculate with the first worksheet's values.
For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange
How do we do such a calculation/formula?
I have a Sumproduct formula that should be resulting in 0. However, it is resulting in a very small number. I have 2 columns of data. The first one contains -169.5, -123.4, -34, -67.4, 169.5, 123.4, 34, and 67.4. The second column contains all 1's. When I create the following formula, =SUMPRODUCT(H27:H35,J27:J35), it results in 5.68434E-14.
Sometimes, when I have different numbers in the first column, but it still adds up to 0, the Sumproduct works.
What I need to do is count the number of cases and values of everything written between the first of January and today (242).
I know how to do it by counting the days from the 1st of Jan to today but how can I do it so it automatically updates each day.
This is what I have at the moment.
The product name is in K, the date is in H and the value is in M. The range is from cell 7 to cell 94
=SUMPRODUCT(--($K$7:$K$94="Pension"),--($H$7:$H$94<=TODAY()),--($H$7:$H$94>=TODAY()-242),$M$7:$M$94)
Maybe I should say conditional product of conditional sum. Eitherway, the values in column J are summed if P is column R for the repective row.
View 4 Replies View Relateduses CSV files for importing product data, problem is both have it formatted differently. What i ideally need is a master file that i can change whenever a price needs changing or product adding, this feeds down into 2 csv files that are formatted for the relevent softwares and then i can use a macro to upload these automaticly at end of day. ive tried using just excel but had no luck, i tried using access and custom reports but keeping getting errors as page size is 35 fields long and doesnt want to export as a csv,
I have Workbook A and Workbook B(csv file)
In workbook one I want to look for a unique code that is in both workbooks and if it is then return a result of the total.
As an example
WORKBOOK A
a0YC000000YWPE1MAP
WORKBOOK B
Code qty
a0YC000000YWPE1MAP 10
a0YC000000YWPE1MAP 1
a0YC000000YWPE1MAP 14
Result I need is 25.
What to type if it is a different workbook and not just a sheet.