Add IF Statement To EXISTING Formulas Over Specified Range
Nov 22, 2006
I need a script that will search through a selected range of cells and add a simple IF statement.
For simplicity, lets assume the desired range is Q36:Q40. (The range is much larger than that.)
The existing formula is:=IF($Z37="%Sales",Q$9*$Y37,IF($Z37="%BS",Q$78*$Y37,IF($Z37="%YOY",O37*(1+$Y37))))
I would like to keep all existing forumulas, just tack on an IF statement before hand that says if a certain cell (Q2) reads "CoPrep" then do nothing, otherwise use the existing formula. I envision this as =IF(Q2="CoPrep","",IF($Z37="%Sales",Q$9*$Y37,IF($Z37="%BS",Q$78*$Y37,IF($Z37="%YOY",O37*(1+$Y37)))))
FYI, the background to this problem is that I am in the process of making a financial forecasting model that allows for choices of company prepared forecasts, hence "CoPrep", or model forecasts based off of other critera, both of which can be sensitized.
I have lots of data in the form of names, and i want to extract all rows of data for that name. So if name "john smith" appears in column A 7 times, i retreice 7 rows of data.
This works perfectly and it gives me my 7 rows of data if i type in "john Smith" into A10, however, i want one more condition to be met in column C, whereby Column C is made up of either 1, or 0. If its a 0 i am not interested, if it is a 1, i am.
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
I have a named range "ABC", range is ("A1", "A3", "A5"). Now I need to use Macro to extend the range to ("A1", "A3", "A5", "A7"), that is, adding "A7" to the existing named range "ABC".
My problem may seem similar to "conditional sumif", but I could not solve it with that approach. The examples of sumif or sumproduct always use
E2:E25="Low"
notation, in my case I need a range on the right hand side of that comparison. I have several brands which belong to some producers. I'd like to sum the values of all brands belonging to 1 producer. The brands and their producers are listed on the same sheet. An example is attached.
how do i go about creating a macro to copy a range in sheet 1 in workbook A and paste that to a new tab(the last one) in the existing workbook B. i would like the tab to be renamed based on a cell value. preferably workbook B doesnt have to be open, and the save and close it
I have created 500+ defined names that refer to worksheets that do not exist (yet). When I add the previously nonexistent worksheets, the defined names that refer to them are not "live" and do not work in functions where that name is used. If you go into the defined names and click on the ones that refer to the now existent worksheet, they start to work, but I was hoping there was a way around this step (e.g. a simple macro that says "update all defined names").
I created all the names so that the person who will eventually be using this will only have to add an appropriately named worksheet and put the data in...then everything will automatically work. I do not think adding names is difficult, but the person who will be using this does, so I want to avoid that person mucking around in my names.
I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?
This is hard for me to articulate, so hopefully someone will understand what I'm trying to do.
I'm attempting to construct a formula in Excel, that basically says: IF the value is between these two numbers, multiple them by this percentage, or if the value is between these numbers, multiple it by this percentage.
So, let's say cell A3 equals 10,000,000, A2 equals 5,000,000 and A1 equals 1,000,000... Also cell B3 equals 10% and B2 equals 5%. We'll say that number X is in cell C1. If the number X is between 10,000,000 & 5,000,000 it should by multiplied by 10%. If number X is between 5,000,000 and 1,000,000 it should be multiplied by 5%.
I've tried a formula similar to: IF(C1 (Rng(A3:A2), C1*B3) ---- Meaning that if the number is between 10,000,000 and 5,000,000, multiple the number by 10%.
I am trying to loop through a range, checking for a cell to contain one of two characters and then put a value in a cell to the side (well 3 away) of it. Im getting an error for Type mismatch on line:
VB: If CellContent = "h" Or "v" Then
the data in the cell is an along the lines of '12.34.56.43 som-thi-vh-ng1'
VB: Dim MyRange, MyCell As Range Dim CellContent As String Sheets("Sheet1").Activate
I am trying to do a calculation based on the conditions of two cells but one cell I would need the range of the report. Either way, here is my current statement.
=IF(P2:P15 = "Green Building 15",SUM(COUNTIF(C2:C15,"Over AC")+COUNTIF(C2:C15,"Top Lab AC")),0)
I get a Value# error (though it systematicaly works if you check in the funtion area), and its because of the range I am using, is there anyway to bypass thiss issue or can someone give a better calculation.
I have tried over and over to get a point value from the percentages posted below but my formula will not return consistent results. Am I doing something wrong.
94% - below 1 Point 95%-96% 2 Points 97% 3 points 98%-99% 4 Points 0% or 100% 5 Points
Reference Cell is AG2 and is .99 but for some reason I return a 5 =IF(AG2=0,5,IF(AND(AG2>0,AG2<=0.94),1,IF(AND(AG2>=0.95,AG2<=0.96),2,IF(AG2=0.97,3,IF(AND(AG2>=0.98,A G2<=0.99),4,5)))))
Need all numbers in between the ranges set. So if I input for example 8765 then the cell still comes up with 18.
IF Cell E25 is 0-7200, then 12, If cell E25 is 7201-14400, then 18, if cell E25 is 14401 - 21600, then 22, if cell E25 is 21601-28800, then 28, if cell E25 is 28801 - 36000, then 34.
so on sheet 1 i have a list in column b (as below) and in column G I want to put a note "not included" if the cell matches what is in sheet 2:
AAA BBB CCC DDD EEE
On sheet 2, column A I have a list of items that I want to not include which I want to be able to add/remove/adjust over time. For example:
DDD EEE FFF LLL
I can get it to put in the correct text into sheet 1 with the following but I don't know how to get it to look through all values on sheet 2 and then only enter the "not included" on sheet 1 if it is listed on sheet 2.
So far I have
Sub checksheet() For Row = 1 To 428 For col = 2 To 256
[Code]....
This only does it for what is written in cell A3 on sheet 2 and does not keep going down the list in sheet 2.
I think I need to define the range better on sheet 2 and ask it to look through that, but im not too sure.
I was making a simple spreadsheet as I'm taking practice tests for some IT certifications and the spreadsheet I was making was to chart out my progress.
I had a range of scores in some cells and one of the formulas I used so that I didnt have errors in the sheet like div/0 went like the following:
=if(b5:b50="","",sum(b5:b50)) this seemed to work really well for the one section, however I tried to get a little fancy as always and I think I messed something up. Beneath that I tried to separate each practice test with the same sort of formula resulting something like the following:
=if(b30:b40="","",sum(b30:b40)) this however just returned an error #value. I dont understand why the first formula worked and the second didnt. Then again, I'm not really an excel guru, back in college I was really good with it but I dont really remember much.
I need a formula that will return "true" only if ALL cells in the specified range are not Null (<>""). I'd like to assign a value (% complete) only after all tasks under a phase have been given a completion date.
I have a problem where I extended a formula down to over 40,000 records which has increased the file size substantially. I only need it to scroll down to a few thousand rows now that I realized that there is alot less data to populate the worksheet. Is there any way to get it back to a scroll range that is more modest in size?
I have a range of weekly data that I need to copy into another workbook, paste it below the data that already exists there and then delete the data from the original workbook. I would love to do this with vba but after hours of searching on how to do this my brain is frazzled.
I have 50 workbooks that I need to import weekly into one master sheet (Master.xlsm) but they need to be done individually after the weekly data has been checked. The master sheet will therefore have existing data and the new data needs to be appended at the bottom. Also column A will be blank in both workbooks so to find the last used row it will need to look in column B.
The number of rows in the weekly sheet will also vary rather than be a fixed range so I guess the last used row will also need to be found there too.
The attached file is a cut down version of my working file showing where the data starts on Row 14, I won't need to copy the headings.
I have a sheet with thousands of rows and 45 columns and I need to delete a range of cells (G:Y) if the cell in column V = the cell in column X, and I need to do this by row. I think an if/then statement would work.
I'm after a formula that will look at a number of cells(that are not in a range) with the word "C" and if they all = "C" then I want the cell to come back with the result "yes" otherwise "no".
If C36, C37, C38 or C39 contain a 0 then put 0 if not continue with the the formulae I have this but I know its not right as this is a sum: =IF(C36:C39=0,0,ROUNDUP((C36/C37)+(C38/C39),0)).
I've created an IF statement with multiple formulas, but I can't seem to figure out if it is possible to pull the information by a specific date range. I have sales reps that visit customers on specific dates and I need my tally sheet to only pull dates within that current week. Does anyone know if this is possible??
This is what I have so far: =SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales rep!P2:P216=I7,IF(sales rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))
If you look above I have it to calculate the date value starting at 03-18-05, but I would like it to only pull that date through the next week.
