In my spreadsheet, certain transactions are labeled as "Fees" in Row G. I need any amounts in Row D to be negative if they are labeled as "Fees" in Row G.
I could simply find them manually and make them negative, but the logic needed in this question will help me with similar problems I am facing.
I am thinking it is probably some combination of the "IF" command.
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
View 9 Replies View RelatedI have a "memo of meeting" excel document used to capture notes during client meetings. In this document there's a cell for "purpose", "agenda", and "notes". The 'consultation type' cell is a drop-down populated by values set in Sheet2. The 'agenda' & 'notes" cells are then populated with information (also stored in Sheet2) based on what drop-down option was chosen in the 'purpose' cell. I'm currently making this happen with basic =IF() statements; however, the problem is that by doing it this way, the =IF() statement must reside in the actual cell which will end up showing the data.
This is undesirable as there may be additional notes, agenda items, etc. that I'll need to add during the meeting and as it's currently setup, I can only have either the pre-populated info OR the ad-hoc notes - not both. It seems a simple fix. Maybe put the =IF() statement in a cell off to the side but have it "assign" the value to 'agenda'/'notes' but that doesn't seem to work.
I have a spreadsheet of ~5000 rows of data that I would like to separate based on the product code column (11 product codes - 43,301,304,313,332,334,979,984,985,986,992). As it stands, I've been copying the sheets, then removing the data by filter, but doing this every week seems to be a waste of time.
View 9 Replies View RelatedI work for a UK charity and have a list of funders in an Excel 2007 spreadsheet.
One of the columns refers to the date on which a new application for funding can be made to that particular funder.
In many cases new applications for funding can't be made for 1 or more years since the last application - sometimes as many as 5 years later. How to get a cell to refer to the date that it contains.
For example, say I have in cell A1 "The Acme Funding Organisation" and in cell B1 (i.e. the "Reapply when?" column) a date of 01/04/2013 (British date format, i.e. 1 April 2013) then what I want Excel to do is to look at the date in cell B1 and if that date has been reached to highlight the cell red. That way I'll know that the reapply date has been reached & that a new application can be made.
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
Ive also attached the worksheet
I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
But Excel does not like the formula at all.
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies View RelatedI need to write a condition in such a way that cell IF A1= "DBR","BPR","SLR","SKR" then change cell B1 to SPECIFIC COLOUR Each condition different colour. A1 values ("DBR","BPR","SLR","SKR") is a list of selectable values. if the condition is falls then B1 should be normal (white color cell) with 0 in it.
View 1 Replies View RelatedMy goal from image is to enter a number for example in cell F4 and have in appear in L6 so simply L6=F4 but I want it to be negative if cell M2 reads "Outgo" and positive if cell M2 reads "Intake" as selected from the list. Is this possible?
View 5 Replies View RelatedI am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.
I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..
I've done the following:
Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub
However, this doesn't even work.
When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
View 2 Replies View Related Basically, I need to reformat a set of data (see before and after). A specific column should only contain 1 value, if there is more than 1 value it will be separated by a comma. If this column contains more than 1 set of data then I would need to insert a row and duplicate the information based on how many different sets there are. All the copied data should remain the same with the exception of the "Key" column, it should only have 1 value and each copied row should contain the corresponding value in the key field.
BTW - it could be on the same sheet or a different worksheet (doesn't matter)
BEFORE
Name Number KEY Date
Name1 1 a,b,c 12/1/2011
Name2 2 x 12/1/2011
Name 3 5 one,three 12/1/2011
AFTER
Name Number KEY Date
Name1 1 a 12/1/2011
Name1 1 b 12/1/2011
Name1 1 c 12/1/2011
Name2 2 x 12/1/2011
Name 3 5 one 12/1/2011
Name 3 5 three 12/1/2011
I have a chart full of different names which I need to separate them by color when a specific entry is found in a Cell. For example the list has the store name "ZELLERS" + its branch number. (ex. ZELLERS #276 PL or ZELLERS #295 SL and so on). I would like to modify the below code so that It only check for the name ZELLERS and not the branch number. If it finds ZELLERS in any cell In the Range of C1:C500 then color it Red.
Below Code does that but I have to write the exact name as it appears in the cell otherwise it wont find it for me. Is there any way to resolve the issue? I am not an Excel Programmer but Only an Excel user.
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("C1:C500")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("ZELLERS #276 PL", "ZELLERS #295 SL", "Gopher", "Hyena", "Ibex", "Lynx")
nums = Array(3, 46, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r..........................
I am trying to create individualized worksheets based off a master worksheet. I have different committee names that I want to appear on the individualized sheets, based on whether the person is apart of that committee or not. On my master sheet, I choose the persons name under the committee and I want that committee name to show on the individualized sheet. For example, I want A1 on Master to show on Name 1, B3 and A10 on Master to show on Name 1, C3. Can I write a formula to achieve this or or am I asking too much? In my actual file, I will have about 10 names and 5 committees with one person being on up to all 5 of the committees.
View 1 Replies View RelatedI have an invoicing spreadsheet, what I want is to have a main working page and then tabs after separated by vendor, but save myself a bunch of manual work in the future.
I want to be able to work in the main tab and the other tabs automatically update with the new info.
So I want the macro to search the text in the first column and if it finds "AIM Land Services Ltd." then I want it to populate the appropriate tab, in this case "AIM", with all the respective information after it ie: A-O. But I dont want it to select any other vendors/ info.
This is what I have:
Sub MoveData()
For Each cell In Range("A1:A1") ' Where to look for the date
If cell.Value = "AIM Land Services Ltd." Then ' The date
Range("A5:O350").Select ' What is the range to copy
Selection.Copy ' Copy it
Sheets("Invoice_Log").Select ' Select the Sheet it applies to
Range("A5").Select ' Where to paste the data
[Code] .........
Im trying to use an event change to change the sheet name based on a cell value, but my issue is how can I error trap if the sheet name is a duplicate? Here is what I have so far
Sub ChangeName()
On Error GoTo errhandler
Sheets(1).Name = Sheets(1).range("d10")
Exit Sub
errhandler:
MsgBox "sheet name is already exists"
End Sub
Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.
View 5 Replies View RelatedActual
Result
london#123;new york#34;
london;
new york;
delhi#145;chennai#54;
delhi;
chennai;
hyderabad#748;
hyderabad;
[code]....
removing the # and numbers from text and add new line after ; symbol. So it would save me a lot of time in preparing status.
CAB HELP.EXCEL.xlsx
We are looking to have the unit number yellow box (A1) change to match the unit status box color (I15:I19) with RED being first priority then ORANGE, YELLOW and final GREEN. If any-one box is say 'Inspect" ORNAGE and the others are all GREEN, the unit number should go ORANGE; and so on. If we can get this working then we want to do a MASTER that matches in the same way that would just have unit numbers. There will be several hundred by the time we're done!
working this formula out for use in conditional formatting;
Make E9 show E9+H12 if H9>0
I've got the following formula to conditionally format E9 to red fill when H9>0, but it won't change the contents of E9 to E9+H12 under the same condition;
=IF(H9>0,(H12+E9),"")
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies View RelatedRow B is a formula: Copied from B10: =(-E10/($G$2+0.01))+((F10*24)/$G$3)+G10
But I need this value changed in the respective column based on the text of row "D". If it contains "R15", then I basically want the formula to be multiplied by 90% of the original value. And if it contains "R19", then the formula needs to be multiplied by 85% of the original value.
Screen Shot 2013-12-15 at 2.45.51 PM.jpg
Bid Sheet.xlsm
I have a shape on one sheet. Need to change the name of a shapes depending on a value in cell from an other sheet.
View 6 Replies View RelatedI have a spreadsheet I use to track supplier information. On the second tab of the spreadsheet, I have a table that is auto-populated based on the first spreadsheet and is linked to a KPI reporting form that I run for each supplier every month. The information on the report changes as a supplier is picked using a simple data filter. I would like the supplier name on the KPI form to change to the supplier that is picked from the filter on the first spreadsheet.
View 4 Replies View RelatedI need to include a prefix of +91 and +911 based on a text in column A. If any cell in column A has a specific text and when I enter a number in corresponding cell in column B it should add a prefix number as below.
If any cell in col A has text "STATE LINE OR MOBILE 1" then the corresponding cell in col B should add a prefix of +91 to whatever number I enter in that cell of col B.
If any cell in col A has text "MOBILE 2" then the corresponding cell in col B should add a prefix of +911 to whatever number I enter in that cell of col B.
If any cell in col A has any other text then the corresponding cell in col B should be the same number as I enter in that cell of col B.
I'm putting the sum of 3 cells in a 4th cell(D2). If the sum is greater, I would like the sum to be the color green. If the sum is less, I would like the sum to be the color red. Is this possible?
(D2) has change value, I want to compare the new value against the existing one.
I have posted example work book
Sheet1 has a list that changes based upon input in Sheet2
So, I have reference pictures on Sheet2 that I would like to show on Sheet1 under the corresponding value.
Explanation: on Sheet two a value is assigned (in column B) to each category (Column A) and then it is ranked (Column C)
Sheet 1 lists the categories in rank order.
I want pics to show under each category.
I saw some articles online, but have been unable to make it work.
I need to change the colors of each tab based on the cell value in G132.
1>Green
1