I have a workbook that I am working with and I need to formatting the cells so that they will have multiple formulas and I am not sure how to make it work.
Sheet1 Column A is titled 'accounts'. There are 2700 rows under Column A that are being used. Column A is already formatted with a Vlookup formula so I can't add another formula in these cells.
What I need to do is highlight many ranges of these 'accounts' cells depending on their account numbers. I need to input this formula in a separate location on the spreadsheet. For example; If 'accounts' is >=110031100000 AND <=110031100099 then highlight those accounts in 'RED'. and If 'accounts' is >=1200454000000 AND <=120045400099 then highlight those accounts in 'green' and so on...I am not sure how to word the if statement. The formula must reside in column D or greater and not in A.
Am I going about this the wrong way. What I am trying to do is isolate the cells (accounts) that I have referenced on another worksheet so that I can visually notice any accounts that may have been excluded.
Col A - budget items (description) Col B - budget dollars Col C - actual dollars Row 10 - summation Cols B and C
When the budget is prepared the actual dollars in Col C are equal to the budget dollars for all items. For instance the formula =b2 is placed in cell C2. As time passes the user records actual dollars in Col C for each item by entering the actual amount directly into the cell. Actual dollars do not become known all at the same time, so that Col C will contain a mixture of formulas and entered numbers.
How can I highlight the cells in Col C that have entered numbers rather than formulas.
I'm getting a time data on the format "0h 00m 00s" but I can't convert it to a standard "00:00:00" format to properly handle it and I'm not so Excel savvy. I already tried looking for something on google but the formulas for similar issues "0h 0s" or "0d 0h 0h" are not working (I can't find a way to adjust them). For example: I'm have the following data on G3:
A1/A2 is Monday, A3/A4 is Tuesday, A5/A6 is Wednesday, A7/A8 is Thursday, A9/A10 is Friday, A11/A12 is Saturday, A13/A14 is Sunday. A15/A16 os Totals. Each of the two cells are merged to hold the heading. B1 is Plan, B2 is Actual, B3 is Plan, B4 is Actual, B5 is Plan, B6 is actual and so on.
Here is the problem: The formula suggested was =SUM(A2:G2)/COUNT(A2:G2) which was fine when I only had the one column for "Plan", but now I have added a second column for "Actual" next to each "Plan" but I still need a formula that will add every second cell and average it out allowing for the odd time that one of the cells could contain text indicating a holiday and therefor be one less day with data and require to be divided by 6 instead of 7 for example to get the average.
I have all these spreadsheets from different vendors and all of these spreadsheets are in totally different formats. Is there a way I can create one macro and apply this one macro to all spreadsheets to get me a standard format?
I have a worksheet with conditional formatting to shade (with the color grey) every nth column using the =MOD(COLUMN(),5)=0 function. I need to manually add cells formatted with various other colors to my worksheet. Some of the colored cells overlay the conditionally formatted shaded columns and the shading shows rather than the colors. I want colors to show on top of the conditional shading.
I'm copying data from another source to excel, and for certain date entries they come as text in the following format "On 14 May at 8:00". Is there anyway to convert multiple entries like this into a standard date format? With or without time is fine - just the date will do.
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value? eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm. Other than creating a table and using a vlookup function
I have a standard mail format which was saved in my hard drive(mail includes attachments and few contents in body) via excel. I've several vendor (#)numbers in my excel sheet, everyday I've to send a mailer to different vendors with necessary documents.
1. Vendor number EX: 12345 was hyperlinked, when I click on that 12345 standard mail format should be opened in outlook and the Vendor number 12345 to copied as TO:12345 CC: defined names in the outlook mail.
how to convert the JD Edwards Julian date format into standard Gregorian date, and can it be done using VBA? Note that the JD Edwards Julian date is different than the standard Julian Date and uses 6 digits instead of 7.
I have an excel sheet where I do a simple price-discount calculation for a product. Please check the file at:
Code: [URL] ........
A2 shows the price, B2 shows the discount and C1-L1 shows the number of units sold. C2-L2 is where I want to calculate the earnings based on the formula I have in C2. I want to apply the same formula to the entire row so that I can quickly see the earning for any number of units sold.
So right now I have a formula that I'm using in column "C":
=IF(B4="","",IF(AND(B4=22),"IN RANGE","OUT OF RANGE"))
So basically column C tells you if the number in the B column in within the range of 22-30. Now I want to write the formula in column D and make it apply to columns B and C.
So cells in columns B and C will have a number value in it and I will make column D where the formula is. There will be a number in B or C, but never at the same time. I can't figure out how to alter the formula to do this.
I am trying to update a project tracking sheet at work and I am having trouble. I have a list of construction projects (approx 130) along with details on the project, one detail being % completed. I am trying to create a summary of the projects at the bottom of the page, showing the total value of all projects, according to the % completed.
<10% is ANTICIPATED = 10% is AWARDED >10% <90% is IN PROGRESS >90% is COMPLETED
I use the If - Then statement in VBA to determine a condition which works perfectly fine for the particular cell I reference to, but i need it to apply to all the cells i.e. cells A1:A10?
Sub MACRO1()
If Range("A1") = "PAYE" Then Range("B1").Formula = "=C2 * .128" End If
If Range("A1") = "LTD" Then Range("B1").Formula = "=(C2 - 100)* .128" End If
I've applied a basic multiplication formula =D12*E12 in a table Column F, also I've filled it throughout but now if new rows are inserted in between or at the end of the table, new cells in this columns doesn't include this formula.
I have a formula that I wrote in B2 and then applied vertically to a large list of rows (100+). I applied it vertically by simply double-clicking the cell with the formula, and it cleanly applied the formula to every row in Column B.
This formula now needs to be applied horizonally across columns, for each respective row. I need a way to do this in one swoop, as it would be very time consuming to manually drag each cell in column B.
I've attached a sample spreadsheet with a simple formula just for the sake of an example.
I'm putting together an Environment Threat Assessment for work and want to automate as much of it as I can to alleviate the amount of time ppl have to spend putting values in. My main problem is I have three very important columns: Likelihood, Success Rating and Risk Rating. Likelihood and Success Rating values will be selected from the following:
VL - Very Low L - Low M - Medium H - High VH - Very High Each of these is given a value: VL = 1, L = 2, M = 3, H = 4, VH = 5. The Risk Rating is the result of multiplying the Likelihood and Success Rating. So a Likelihood of Medium and a Success Rating of Low will give a Risk Rating of 6. Therefore, the cells on my spreadsheet would look like this:
Likelihood Success Rating Risk Rating M L 6 The issue is I want a couple of things to occur. Firstly I want keep the values in my Likelihood and Success Rating cells as letters (ie. VL, H, VH etc) rather than numbers, while still generating a number result in the Risk Rating cells. Secondly I'd like to be able to change the colour of the Risk Rating cell to reflect the number it gets assigned. For example Green for < 8, Amber for 9 – 14 and Red for 15 – 25. I've been trying to do this with array formulas and am basically going around in circles with no success.
I am using two separate workbooks. I am tranferring dates into a new spreadsheet to track projects dates for milestones.
The formula I am using is this: =IF('[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$CQ$4="", VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 94, FALSE), VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 95, FALSE)).
I would like the cell to turn green if the last part of the formula is true and stay clear if the first part of the formula is true.
I also need to add color beyond just that. I was attempting to apply conditional formatting but am a bit stumped. I want the green to be maintained regardless of other formatting. I would also like to apply to cells that are not color coded green:
Red - if the date is overdue Yellow - if the date is within 7 days Otherwise, leave the cell color as clear
Is this possible with conditional formatting? I am not at all proficient in VBA...
I have thousands of path names in a spreadsheet that were converted to hyperlinks, but my client wants to see only the filename in the hyperlink, not the whole path.
I created the hyperlinks by applying the following to the column containing the file paths: ....
I'm new to creating macros and trying to create a macro that will perform a formula that I can use for multiple files. However the files are a varying number of rows.
I want the formula to carry out on the entire column (all rows). However when I record the macro (I tried using relative and also tried using absolute references when filling the formula) the formula always seems to stop filling wherever my original file's rows stopped. I wrote the formula for the first row and double clicked it to fill the remaining rows.
For example if the macro I recorded in File 1 goes to row 15, when I run the macro on File 2 which is 20 rows, the formula stops filling at row 15.
