Column A on my Order sheet is an entered date and L13 is a specific date criteria - this works fine on its own
Column H on my Order sheet is a calculated value (cells contain a formula) - this part does not work
I have tried changing the "*" to "," as I have seen suggested elsewhere but this does not work either. I believe that the SUMPRODUCT function is having trouble with the H column because it contains a formula and not an entered value. How to make this work or another way to accomplish the desired outcome?
I receive montly investment performance projections going out monthly for 10 years. I need to show the return annually for each year. I created a SUMIFS formula to sum the percentages between two date ranges (beginning of the year and the end of the year for each year). However, my SUMIFS formula does not take into considersation compounding. How can I modify the SUMIFS formula I created to take into consideration compounding?
I know how to convert monthly percentages to an annual percentage,using the PRODUCT formula =PRODUCT(1+T15,1+U15,1+V15,1+W15,1+X15,1+Y15,1+Z15,1+AA15,1+AB15,1+AC15,1+AD15)-1, whereas T15:AD15 is the monthly percentage gain. Is there a simplier/alternative way that I can incorporate into the SUMIFS formula?
If it is too complicated to utilize a SUMIFS formula, I would like to be able to select a range of cells and have Excel calculate the result vs typing "1+[select the cell]," for each month. It takes me forever and open to input errors. It would be much easier to be able to select a range of cells when creating the formula.
I 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:
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.
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.
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.
I'm trying to find out exactly how I can use sum product on cells that have text and numeric values. Here's my sample data (6 points):
TN FX1576 20, TN FX1577 25, AZ FX1577 30, AZ FX1577 35, FW FX 1577 40, and FW 1577 45.
I wish to do the following: I want to sum all of the right numeric values based on the first two text values(TN, AZ, or FW). I want to sum these numbers to their respective total cells at the top of the page. So far I can sum the numeric values fine using the following formula: =sumproduct((right(range,2)*1) but when I try to differentiate between the respective locations (AZ, TN, or FW) I get an error message.
Attached is an example of what I'm trying to do along with the formula I'm currently using and yellow shading to represent where I want my respective sums to go.
Macro - it is currently set up to find a specific text, and activate that cell. From there, the spreadsheet has data in 6 cells in the 6 rows below the active cell. What I need the macro to do is to identify the product based on key words in a range of cells: that is, if any of those cells contain the string "total", than the product type is total, but if "total" is not found, look for keywords such as "alpha" and "beta", to identify the product type. If its not a total product, then i want it to look for "productB", "productc", and so on with relevant keywords until the product is identified.
I think IF(ISNUMBER(SEARCH can be used, but I'm not sure how, and I'm not sure how to search all 6 cells (to make things worse, the number of cells vary, so I'm using the "find" function to figure out the first and last cell that contain data).
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
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
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?
Macro to clear cells with numbers but no cells with formulas with in this macro:
Dim i As Long i = Range("E3") If i > 0 Then ' Copy range Range(Cells(6, 10 + i), Cells(500, 17)).Copy Range(Cells(6, 10), Cells(500, 17)).Select ' Paste special ActiveSheet.PasteSpecial Format:=2, Link:=1, _ DisplayAsIcon:=False, IconFileName:=False ' Clear i columns on the right Range(Cells(6, 18 - i), Cells(500, 17)).ClearContents End If End Sub
The range is where the cells with numbers need to be cleared but not the ones with formulas.
I am trying to find a way to match formulas from two cells (not the value but the formula). I am creating a smaller excel test where they can enter a formula (C3) and directly see if it matches the correct answer (F3) All I have found is formulas containing value comparison.
How do you force excel to recompute for the formulas in the cells. I know excel automatically does it however running the delay in recomputing the formulas is making wrong reference. I need to monitor the location of the cell using the match function.
Lets suppose i have 5 columns of data. 3 of the cols have "formulas" in them and 2 do not. I want to count the number of formulas that are in a given row. Is there a way to do this?
I am trying to move a group of cell containing formulas from one sheet to another, I have tried copy & paste/ ctrl & alt / paste special but for some reason the initial cell references of the formulas are not updated.
I am trying to adjust a simple formula that I am using:
=IF(D3<>"",1,"") **Note this is clunky, but ISBLANK doesn't seem to work due to D3 containing a formula**
to also change the fill color of A3 to "Red" if data exists in D3. Everywhere I look online for the formula reference tries to point me to Conditional Formatting, Which doesnt seem to apply to seperate cells so I can't use this.
I have a column of formulas I'm trying to sum. The SUM function gives me zero.
The formulas are simply pointing to another place in the spreadsheet so the contents of the column look like this:
=D11 (And it displays what's in D11... $1432 etc etc) =D28 =D30
When I sum those I get Zero. Is there a better way to do this? All cells are formatted as currency
Also... cell D11 is actually a sum of different cells. (As are the rest of them) So I guess what I'm saying is that I'm trying to display the value in cell D11 and D28 and D30 and sum up the total.
I have a spreadsheet that has formulas in cells C3 to C10 (cells C3 to C10 have =(sheetc), (cells C3 to C10) formulas. I want to be able to add a row somewhere in between those cells and have the that new row take on the same cell formula as the others...resulting in cells C3 to C11 now having formulas..
how can I use a formula via VBA instead of putting it in the cell itself? It is causing me problems because when I put a formula in a entire row, all the cells five me the number ZERO, when I want them to stay without any number until I input something in the other row's cell in the same line. I will give an example: I would like that anytime I entry any number in a cell in column A the cell in column C will give me the number in column A times the number in column B (which is a variable daily updated via an internet connection), and when the cell in column A becomes "null" (that is when there is no number in the cell, not even a zero) the cell in column C also becomes null.
When I try to do it using formulas in the cells I always get a ZERO in all the column cause they read a 'null' as a 'zero'. Trying it using VBA I have some success but still have problems. I tried this code
If Target.Column = 3 Then Cells(Target.Row, 8) = Cells(Target.Row, 3) * Worksheets("Sheet4").Range("d32").Value
But it makes the cell in column 8 be static and change only when I click on the cell in column 3. I need it to change automatically when the number in D32 is changed too. As a formula in the cell would do.
I currently have my template protected, and I've been getting complaints that they can't modify the width, fonts etc. The only reason I have it protected is because I don't want them to mess with my formulas. How can i protect ONLY the formulas, and have the rest editable.
On some worksheets and spreadsheets, when I save the formulas disappear, only to be replaced by the previously calculated values. It has become a real pain.
i wonder if there is a way to compare the formulas in two cells and not their result. I use a worksheet as a model and i want to track unwanted changes in the formulas between any new worksheets created and my original worksheet.
I have a niggling problem with a worksheet when using Hlookup to return a value from a range of cells it is coming up with blanks when there is data in the range.
There is only ever one row cell containing text in the specified range and i need this to be returned in another column.
The problem with the hlookup formula i am using is that it will not work if cells contain formulas and in the range i am working on all cells contain formulas.
I know this formula works if there is text without a formula in the cell range, as soon as it hits a cell with a formula though it won't work.
I have attached the sheet : Book4.xlsx
From j138 there is one cell with text i wish to be returned in column q for each row. E.g., in q1 the first cell text returned would be khawatim. q2 should contain cantor etc etc.
