I'm running the following to put quotes around data in a workbook:
Sub test() For Each s In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row) s.Value = """" & Format(s.Value, "00000000") & """" Next For Each s In Range("B1:B" & Cells(Rows.Count, 1).End(xlUp).Row) s.Value = """" & Format(s.Value, "####") & """" Next End Sub
And exporting as a CSV file for loading into another program. The problem is everything looks OK in Excel, but when I open the file in Notepad to check the output, the quotes are all triple. How can I get only single quotes around the data?
I am trying to figure out 3 differnent percentage categories but I cannot figure out what formula or conditional formatting that I should be using. The scenario is as follows:
For example, Jamaal is authorized for 80 hours per month but only uses 15 hours.
What I need to figure out is if he is below 25% of hours funded, then he will be exited from the program. If he is between 25% and 50%, then he will be warned. If he is between 50% and 75%, then he has excellent attendance.
Therefore, I came to the conclusion that: 0.25% x 80 = 20 hours 0.50% x 80 = 40 hours 0.75% x 80 = 60 hours
Which means that Jamaal is in the below 25% category, meaning he will need to be exited, unless he improves his attendance. But what I want to show on my spreadsheet, is a color RED for below 25%, and other colors for the other categories. But I don't seem to be able to conclude it all into 1 cell. I tried using the Conditional Formatting and the below 25% works but once I add a new rule for between 25-50, then my rule for below 25% doesn't work anymore. I don't know if an actual formula exists for this. Does will be a monthly spreadsheet that I will be working off of so it would be nice if all I do is input the total hours used and BOOM it tells me if the client is below 25% hours used or above.
Have 2 conditional formattings going on in a database query and need to apply two more conditions but only have 1 conditional formatting left. I am trying to highlight everything in a row that contains either "HCAT" or "HCAL" in column D. So far I have this, =$D2=OR(HCAT,HCAL) but its not highlighting anything, any clues?
Hello, I cant figure out how to do this! I have a cell B2 that I enter a number in and in cell C2 I want it to add the number from B2 and add 15 to it. I can get the formula "=B2+15" and it works but I wont always have a number in B2 and I want it to say "0" and "0" would be in C2. Any ideas?
I simply need to have column A be red if value is < Column B value and green if it is >. The problem is that if the value in column A is .00000004 and column B is .00000005, it turns red. Example is the first three numbers below the headers....they each are 0%, yet the value on the left turns red because it is .0000005 more than the value on the right. I just want the colors to reflect the numbers which show up as below. Is there a way to do this?
1. I have a range of random numbers within a column, i.e. A1:A9 2. A specific number will appear in a cell at the end of that column, i.e. A11 3. I need to see via conditional formatting which of the above cells add up to that number.
I am aware that given the numbers in A1:A9, it is possible to have more than one combination of numbers that add up to A11. I would need to see those combinations as well identified using Conditional formatting.
From this I need to calculate the Covariance of a portfolio's returns with the market index. The problem is that I cant seem to specify a condition within the =COVAR(...) formula so that the COVAR Folrmula automatically selects only the applicable pairwise observations... That is, I need to write a COVAR function that will automatically search the first Column for a specific portfolio number and then include the corresponding returns data in the COVAR formula. For example, the first four formula need to return (B2:B5,C2:C5) and the next three formula need to return (B6:B8,C6:C8). I hope this has been descriptive enough of my problem. I am doing my head in trying to use Index-Match functions but know there must be some simple solution.
Is it possible to write a formula that will compare the text in two different cells? And if I can do this can I also have that formuls place a numeric value in another cell? An example of what I am trying to do is: If the text in C2 is equal to the text in C3 place a 1 in C4 or if the text in C2 is not equal to the text in C3 place a 0 in C4.
I am referencing a cell in order to change the background color of a row of cells if the number in column B is = to the number in a specific cell. The formula used in the conditional formatting is =$B73=$B$10. The reference cell $B$10 contains the following formula =INT(MAX('Assumptions & Inputs'! F8:F9))
The conditional formatting worked fine when I used that formula. I had to change the formula in cell $B$10 so that it would meet a new criteria. When I did that the conditional formatting would not change the color of the row. The new formula that I put in the reference cell $B$10 is
I am trying to understand VBA codes. Actually I wrote a formula with IF and I want to change it to VBA because it is so long and creates some problems. It is too important for me =IF(AND(0<C6;C6<9);"0";((IF(AND(8<C6;C6<14);(C6-8)*(8,4);((IF(AND(13<C6;C6<10000);(5*8,4+(C6-13)*(26,45));" ")))))))
The logic is like that, this program will calculate an amount. btwn 0 and 8 days, it is free btwn 9 and 13 days, 8.4 euros per day after 13 to infinity, 26.45 euros per day
Therefore for example for 10 days, you have to pay 2*8.4 = 16.8 euros for 14 days, 5*8.4 + 2*26.45 = 92.9 euros
I have a challenge with conditional formatting. I have products A-K with sales current year and last year and want to have year on year growth. I want to have one column where the cell formatting changes based on data validation.
Let's say product A sold 60 items CY and 50 LY meaning YoY variance is +10 items or +20%. Hence, if the relevant cell says "%" then the YoY figure should say "+20%" (formatted as a percentage) and if "VALUES" then YoY should be 10 (formatted as numbers/absolute values).
Remember also that YoY growth can be +100% (or more), 0% and -100%.
I am trying to use conditional formatting for a cell; It is a cell using Validation; you can choose "Yes" or "No" from a list If the value is "Yes" the cell should be green, regardless of everything else But if the value is "No" it should turn red, but only if a choice in another cell is equal to "X". Otherwise it should be left without special formatting.
I am having a very difficult time trying to figure out a conditional formatting formula for something that seems very simple. There are 3 conditions I would like to apply to cell B2: If the value of I2 is less than or equal to 24, but greater than or equal to 21, shade B2 red. If the value of I2 is less than or equal to 20, but greater than or equal to 12, shade B2 orange. If the value of I2 is less than or equal to 11, but greater than or equal to 8, shade B2 yellow. I am sure I am missing something quite simple here.
I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total.
The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7.
im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7...
this is the formula I have now, obviously it doesnt work
I am having an issue, because aparently workday formula does not work in conditional formattings. I have the two formulas below to use in conditional formattings, but I can't figure out how to re-write them so that Excel will allow me.
I'm looking for a formula that utilizes an "if/then" condition. I'm attaching a sample workbook of what I'm dealing with. I need it to end up so that column "E" holds: if a equals c, then e = b, "and", d, c Does that make sense? I manually entered my desired end result into column E in the sample workbook.
I have a spreadsheet where I have certain cells that contain a link to copy a number from another tab in the spreadsheet.
At times, I have to plug in a number to try certain values. In doing this, I overwrite the link or formula. I then do an 'undo' to bring it back to the original link or formula.
I would like to put a conditional format based on the cell's content. If it is the formula, leave it as is. If it is overwritten by a number, I want it to change colors to remind me to undo my change when I am done.
The conditional formatting options I have found so far allow me to format according the the value in the cell but not the nature of it's content: number of formula.
This is the most simplest conditional formatting which is working except on some cells. I want to compare two columns cells ex : if A=B , then format A as green.
Its working, the "formula" and "cell value contains" everything except for few cells. Cell B has formula's in it related to other cells. When i enter values in other cells, Cell B computes but A refuses to format "green".
If I just enter same value[ex 1, 1]in both the cells sans formula ,it highlights A green . I want to know , if the formula in cell B is causing the errors in formatting and if it is
i am trying to achieve old products on a database that we no longer have any stock of and we haven't sold since a specific date. I am having probems with an "=if(and" formula. At least i thin it is an equals if and formula that i need.
Basically i need to say the following:
=if(Stock quantity column = 0 and the date field is <= 31/12/2005 then "Delete" or "Keep")
I am working between two worksheets. In one worksheet I have a list of account numbers. In the second worksheet I have the same account numbers in a different order. What I am trying to do is, If the account number from worksheet one is found in the column with the account numbers in worksheet two, I want to display the matching title for the account number that is in the same row but different column in worksheet two.
So essentially (if acct # from column x in wks 1= an acct in number in column m from wks 2, display the corresponding title in the same row of the matching acct # found in column m). Is this possible?
My attached spreadsheet is for tracking when certain tasks were completed on each case and figuring the number of days that it takes to complete these tasks.
The task I am needing help in formatting is the Corrections Received field (column E). We need to track the cases that do not have corrections received within 7 days of the changes being requested (Out for Corrections, column D). The issue is that there would not usually be a date entered in the Corrections Received column when we need to look at those cases...because they would not be received yet. In order to not get an extreme default value (like the -40181 or 40922), I entered the =Now() function in column E (except for first case as an example of what this would look like otherwise).
What I was hoping to be able to do is use conditional formatting to look for that Now() formula. If the formula is present, the cell would be highlighted. If a date were entered, even if it was today's date, the fill on that cell would be removed. That way we could tell when the displayed date is a true date and when it is a placeholder until we receive the information.
Currently, it either highlights no matter what the cell includes or it doesn't highlight anything...