My goal is to sum up the total sales and total forecasted sales of each cost center. I compiled a formula that is supposed to do this just as I want it to. Simple enough. However, for some odd reason the formula returns either the totals of ALL cost centers or just a zero instead of the correct result. The weird part is that when I check the formula by pressing the "insert formula" button it suggests that the formula actually should produce the desired result.
I have created a table in excel, and it has a very simple sum totals. When i include all cells in the table the total becomes 0. When I exclude a cell in the table, the total shows. I have deleted the row and re inserted another one and copied the cell content from above that was not having a problem,
I'm trying to do a simple conditional format where the date is highlighted in red if the difference between that date and Todays date is greater than 90 days.
I've done the formula as =(TODAY()>$B5)>90, which appears to work in the sheet and shows either True or False but when inputting that formula into the conditional format it highlights every single cells.
I have copy 65'000 formula text cells from a software and paste it to excel. But, the formula in cell is not activated. In order to activate the cell formula, I have to click on each individu cell and press enter to active it. But i got 65'000 rows of the similar cells. So, what is the quick way to all the text formula at the same time.
In the attached spreadsheet I'm using VLOOKUP to create a cross reference between worksheets JS and ITEM. If you will look at the ITEM worksheet cell reference H13 & H14. The correct value for H13 should be AMC, not 729. Is there a way to use the value in the Class column and Item ID column in combination to get the value AMC? Would MATCH & INDEX work? I'm not familiar with Match & Index. I'd appreciate some help here. I've got 15,404 records to evaluate this way.
The results of the formula in cell K36 in the attached spreadsheet returns a value of null. It should be $1,200. Am I blind or have I done something wrong. I just can't see the problem with the formula.
I setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE.
I'm using the following phrase out of a much longer expression to demonstrate that the return value of the MID function seems to be nothing I can understand.
returns 6/10 as text which makes sense. However I was under the impression that when text in Excel contains numeric characters, it can be interpreted as a number. So then how does the following expression,
return the result 38878? Does it still think it's text?
My SUMPRODUCT formula is returning 0 instead of expected results =IF(ISERROR(SUMPRODUCT((WOR_Data!$A$2:$A$429=$A7)*(WOR_Data!$E$2:$E$429=$L7)*(WOR_Data!$K$2:$K$42))),0,SUMPRODUCT((WOR_Data!$A$2:$A$429=$A7)*(WOR_Data!$E$2:$E$429=$L7)*(WOR_Data!$K$2:$K$42)))
I have verified that all criteria match criterion for spelling, case, length(trailing spaces). All fine. First comparison is unit, second comparison is account description, 3rd argument of course column to be summed.
If I take out the error handler, I receive #VALUE! error
I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?
I'm attempting something new, a dropdown menu using vlookup to find data . If i use the simple example below, using data from B-D on 1 worksheet, where i want to find people by county:
A B C D Search by County Name County Town John Sussex Brighton Eric Yorkshire York Jim Cheshire Manchester Liz Sussex Hove Sarah Avon Bristol
I've created a dropdown menu on a second worksheet,A2, by county search, to return a persons name,county and town. The problem is if i select Sussex i will get 2 names who live in different towns within Sussex. I've created a simple vlookup which works fine, but because of Sussex having 2 results, i created a second vlookup for the second result to appear in the cell underneath.
The problem is that for the results that only have 1 result, the second vlookup returns a the same result as the first result, which i dont want.
Is there a way where multiple results will be shown if they exist, but for one result to appear if only one result exists?
I'm trying to make a macro to keep track of our rental properties. This macro, in another variant, worked as planned, searching for data in a column, copying the row to a new sheet, and clearing the contents of that row. So I've tried to make it search for a date in a new workbook and do the same. It runs, and tells me it has returned zero significant results (did not find data to move and delete). My column I is formatted mm/dd/yy . And the rest of the workbook is protected. Here's what I have:
Sub CopyReturns() 'Macro written 10/2/08 by Jeff ActiveSheet.Unprotect Dim DestSheet As Worksheet Set DestSheet = Worksheets("Sheet2") Dim sRow As Long 'row index on source worksheet Dim dRow As Long 'row index on destination worksheet Dim sCount As Long sCount = 0............
i have a problem regarding calculated field.. i have data of sales of a distributor with all the accounts that he handles.. i want to get the total number of buying accounts for that distributor..
buying account is an account that bought a specific product for at least one time... even the account buy 2 or 3 times more for that specific product the account is considered as 1 buying account..
i want to use a pivot table so i can manipulate the data.. first i tried to add an additional field to my data and put the formula "if(norbo_val>0,1,0)" where norbo_val is the total sales less the rejects. it means if ever an account have positive sales the account is considered as a buying account for that specific product. my problem is that when that account bought the same product for 2 or more times it sums all the times an account bought for that specific product..
so i decide to remove the field in my data and create a calculated field in the pivot table. i input the same formula to the calculated field and it gives me the correct result where if ever an account bought a specific product for at least one time regardless on how many times the account bought that same product, the account is considered as 1 buying account. the only problem i am encountering now is that the grand total is not summing up the total numbers of buying accounts if i list all the accounts. it seems the formula is affecting the grand total where it should add the total of buying accounts but instead using the formula of the calculated field so the result is only 1.
I have a formula that is correct all the way up to adding the last *K9. here is an
C9 a number 4 then add 1 to it which makes it 2 now * it by D9 which is the number 384 and the answer is 768 now * that by K9 which is 6 and you get 4608 I'm getting 2310 using this formula... =IF( C9=0, 0, C9+1*D9)*K9.
Surely it's user error, but it honestly seems like Excel is not calculating 2 numbers correctly. At work, we have office 2010 where it is not calc'ing correctly; at home I have 2007 and it's fine, but anyway, here's my situation:
Cell A1: 0.003529 Cell B1: 0.48
If i put in Cell C1: "=B1/A1" i get a value of 136.000000 But that's not correct, it should be 136.01587... But if in Cell C1 i put "=0.48/.003529" then i get 136.01587...which is correct.
Why does using cell references in my formula give me the incorrect value?
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
It's been a long time since I have worked with Excell, it use to be the " $ " that made things absolute or relative.
I have this formula:
='1'!C194+C193 on sheet 1 named "1" without the " ". I need to copy it across 31 sheets. I thought that it was the " ! ", without the " ", that makes the address absolute so I took it out and then attemped to copie it to other sheets. The idea was that the formula would then read: ='2'!C194+C193 on sheet 2, and ='3'!C194+C193 on sheet 3 and so on. The formula is in the exact same location on each sheet, C194. But this won't work. I am at this time doing each formula one at a time. I have many workbooks that will use the same style of copy and paste; about 15 workbooks each with 31 sheets.
It would help if someone could explain how to save time. I do know how to use the Ctrl to select different cells and how to use Ctrl+Page UP/Down to change tabs. I just have to get the formula correct.
I have a formula that basically copies a cell into another workbook if 'other' is entered into the column to the right. i,e cell A1 has 'other' B1 has '100', the figure in B1 is placed into another workbook cell. The results are only shown if a month is selected from a drop down on the 1st workbook with the meta data.
What I want to do is to add a command to the formula below so that it will recognise that after the first item is entered it will look for the next data item and not just repeat the first….
I am trying to write a macro that will allow me to copy the text of a cell, and then insert text around that previously copied string. I am having a problem adding the copied text into my formula. Here is my code below:
Sub Bid() ' Selection.Copy ActiveCell.Value = "inventory levels are " & ActiveCell.PasteSpecial(xlPasteValues) & " and oversold by" ActiveCell.Offset(1, 0).Select End Sub