Format Column Based On Entries In Another
In the same worksheet, I would like to format column "U" based on the alpha character in column "T" and also format column "AA" based on the alpha character in column "Z". There are three alpha characters that will be used in columns "T" and "Z" ( upper case and lower case), "A" with the format of $ #,000 & "H" with the format of $ 0.00 & "P" with the format of percent %. I want to format column "U" & "AA" prior to entering a value.
Column "T" & "Z" are a pull down menus containing only the values listed above (a,A,h,H,p,P)
I had asked and received an answer regarding the above pertaining to columns "A" and "B" and was given an answer that works, however, today my boss changed the configuration of the spreadsheet and for the life of me, I could not get the code to work using the new parameters.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Function To Sum A Column's Entries Based On Another Column
Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View Replies!
View Related
Autofill Column B Based On Column A Entries
I have a Listbox that outputs data to Column A. Is there a way to auto populate Column B with 4 items for each Entry in Column A? The 4 items that will populate in Column B will always be the same. I have attached an example to better explain what I am trying to describe.
View Replies!
View Related
Filtering For Unique Entries Based On One Column
This may be a basic question but I haven't been able to find the solution: I'm using Excel 2003. I have a list with repetitive entries in one column and unique entries in the other columns. Example 0001 a 0001 b 0002 c 0002 d 0003 e 0003 f I need to filter the list so that only unique entries in the first column show up, but I need the rest of the information to carry with it. Example: 0001 a 0002 c 0003 f I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
View Replies!
View Related
Named Ranges Based Upon Indent Levels & Entries In Column
I had an idea that I could use the level of indent in the first column to provide the name for the range. The easiest way to explain is with the example spreadsheet I have attached Cells C5:C10 show how the naming convention should look like, basically Indent Level 0 returns a prefix (Sheet_Name_Prefix) and its corresponding row entry in Column A. Level 1 should return the last Level 0 name and its corresponding row entry in Column A. I had a go at the code and it works for Level 0 but I can't get it to put to Level 1. Sub Test() 'Dimension Variables Dim RowTitleEntries As Integer Dim NameLoop As Integer Dim IndentLevel As Integer 'Set the value of RowTitleEntries RowTitleEntries = 6
View Replies!
View Related
Conditional Format: Alternate Row Shading Based On First To Letters In The A Column
I have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command. The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column. For instance: Column A CPST4 CPST5 FLADS3EC FLADS2EC FIL4 CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading. MacOffice Excel 08 is used so no VB code can be used.
View Replies!
View Related
VBA Loop Through Worksheet And Format Each Column As Text, Or Date Based On Header
I am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook Sub Format() Dim VarFileName As String Dim VarPath As String Dim VarSavein As String Dim wsheet As Worksheet VarSavein = Sheets("sheet1").Range("C2").Value VarFileName = Sheets("sheet1").Range("A2").Value VarPath = Sheets("sheet1").Range("B2").Value Workbooks.Open VarPath & VarFileName For Each wsheet In ActiveWorkbook.Worksheets Sheets(wsheet.Name).Select Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ := Array(1, 2), TrailingMinusNumbers:=True..........................................
View Replies!
View Related
Convert Text Entries To Valid Date Format
Please refer to attached spreadsheet I receive data from an external source and it displays what appears to be dates in column B. In column G I apply a formula to display month/year info. It turns out that some of the data in column B is valid date data, but other data imports as plain text and therefore I can't get the month/year info that I require. I have attached just a small example. I get this data monthly and it usually covers thousands of rows and therefore impractical to change manually.
View Replies!
View Related
Conditional Format Table Row If Entries Missing
Please find the attached sheet. I just want to know whether is it possible to condional format multiple cells based on multiple cell values. Eg: In the attached sheet,when a driver is standby and the vehicle no column should be empty,if accidently a data entry is done in vehicle no column it should highlight the whole area (ie,from A3 to E3).
View Replies!
View Related
Speed-up Slow Macro Code: Entries In Column 73 - 85 Will Be Copied From WorkbookRust To The Other Workbook If The Numer In Column 5 Is The Same
my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame. Sub Allmacros() Dim WorkbookRust As String WorkbookRust = ActiveWorkbook.Name ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch" Workbooks.Open Filename:= _ "C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls" Sheets("Main_Overview").Select Windows(WorkbookRust).Activate Application.run ActiveWorkbook.Name & "!UpdateEntries" Application.run ActiveWorkbook.Name & "!FilterMain" 'not ask to overwrite existing file Application.DisplayAlerts = False Workbooks("CH_Revenue_2008.xls").Save Workbooks("CH_Revenue_2008.xls").Close End Sub Sub UpdateEntries()......................
View Replies!
View Related
Change Cell Format Based On Existing Currency Format
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
View Replies!
View Related
Counting Entries Based On Month
I have a column of dates and want to count how many times there is an entry for a particular month. example: (column D) 7/4/2009 7/13/2009 7/22/2009 8/1/2009 8/6/2009 9/15/2009 I want to be able to coun (countif?) how many times there is a July listing, August listing, etc. =COUNTIF(D3:D20,">=7/1/2009")+COUNTIF(D3:D20,"<=7/31/2009") =SUMPRODUCT(--(D3:D20>="7/1/2009")*(D3:D20<="7/31/2009")) I have made them arrays.
View Replies!
View Related
Restrict Time Entries Based On Another Cell
The 'gist' of what I need is for events scheduled (top of spreadsheet), no school can be placed into an event within 29 minutes (because one-half hour apart IS ok). The time increments are on the left side of the spreadsheet (rows). I'm trying to "automate" somehow so that if we try to plug a school into an event too soon from their last event, the spreadsheet won't allow it.
View Replies!
View Related
Trimmean Last 6 Of Variable (by Column) Entries
In column E, I want the median of the last 6 entries in columns K to EJ, where EJ is the oldest entry, and I want to median the most recent 6, that is, the last 6 entries counting inward from column K toward EJ. There will be blanks, and each row will have different columns filled, most columns empty, so I want to median the last 6 entered numbers.
View Replies!
View Related
Select Valid Entries Based On Letter Entered
I tried to search the forum with the above title but keep getting a fatal error. My worksheet contains a data entry column that is verified against a named range entitled "Categories". If you click on the down arrow, it displays all the valid entries. What I'd like to do is enter in the field the first letter of the entry and display only those valid entries that start with the letter I entered. For example, if I entered "c", the validation screen would display: Car Insurance Car Payment Car Repair/Tires Charity Chase Mortgage Church Cleaning Supplies Cleaning/Laundry Contact Lens Cosmetics Then, if I entered "ch", the validation screen would display: Charity Chase Mortgage Church
View Replies!
View Related
Remove Initial Space From Column Of Entries
After copying certain data into an excel worksheet, all entries in a column begin at 2nd space of each cell which I don't want. I can manually backspace each entry to the first space of each cell. But I cannot backspace all entries at once even if I try Find then put in a space for find and then in replace leave blank.
View Replies!
View Related
Copy Rows Into Sheet According To Entries In Column
I copy rows of data according the Workgroup entries in Column C and paste them in to the relevant team sheet so that it is separated and printed off. Currently I am doing this manually, as the report length is variable and can be a few hundred rows or sometimes a few thousand which takes up some time. I know I can use the auto filter option, but as I currently use a macro to format this report I was hoping to include it whilst it is being formatted. Also would like to apply all borders to report so its easier to read when printed and a comments column at the end, but this can be done later. There are teams and 27 workgroups to separate into each team, I have to do this everyday and was hoping to save some time here. I've attached a sample work book as an example.
View Replies!
View Related
Mark Multiple Entries In The Same Column With Numbers
I have many wordlists, which have multiple entries in one column. I would like to mark up entires which appear more then once with numbers 1,2,3....The number should be put at the end of the word or ath the beginning. Before Macro: Column A Love Love Long Lonely Lonely Living Living After running the macro: Column A Love 1 Love 2 Long Lonely 1 Lonely 2 Living 1 Living 2
View Replies!
View Related
List Unique Entries And Total Cells Based On The Results
Basically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other. What I want to do is look down Sheet OEE V20:V500 and get two lots of information - The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this). The number of occurances of each of the problems it lists in Sheets Reports B1:B100. Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100. I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page. I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees. Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.
View Replies!
View Related
Count Unique Entries In One Column That Meet Conditions
I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.] The situation: I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values. namegender jones m martinf smithf collinsf wilsonm jones m martinf hughesm wilsonm martinm smithf west f jones m west f martinm The challenge: In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman! The correct result: ...
View Replies!
View Related
Advance Filter Check Unique Column Entries
How do you remove entries from a column such that only unique entires are left? Say the spreadsheet contains only a single column: david kris tony hayley kristy john david test hello david I tried an "advanced filter" with "unique records only" checked. It gets rid of one david, but not 2 (there are originally 3 of them, and we want to leave 1) This is the result. david kris tony hayley kristy john david test hello
View Replies!
View Related
Finding Duplicate Entries In Column Considering Indirect Referencing
I wonder if there is any easy way of findinig (numerical) duplicate entries in a column? Some cells are empty, in case this might cause a problem. I do not wish to delete duplicate rows automatically, just to find them. Why not just sort it? Because indirect referenceing is used where each row corresponds to a separate spreadsheet in the workbook. What I need is to find the duplicate so that I manually can erase one of the spreadsheets for the particular case and adjusting a reference list.
View Replies!
View Related
Pasting Entries Into Sheets Depending On Data On A Column
Let's say I've got nine entries of data. Column A is aligned like this: One One One Two Two Two Three Three Three What I would like to do is write a macro so that the three entries with "One" in Column A get cut and pasted into a newly-created sheet named "One." All of the Two's get cut and pasted into sheet "Two" and the same thing with the Three's.
View Replies!
View Related
Calculate A 30-day Moving Average Based On The Last X Number Of Entries And Date
I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30-day moving average based on the last (non-zero) value in the column 2. Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.
View Replies!
View Related
Column "b" Format Based On Column "a"
I would like to format column "B" based on the alpha character in column "A". There are three alpha characters, "A" with the format of $ 00 "H" with the format of $ 0.00 and "P" with the format of percent %. I want to format column "B" prior to entering a value. Column "A" is a pull down menu containing only the values listed above (A,H,P)
View Replies!
View Related
Convert Cells With Percentage Format To 'same' Decimal In A Column With Mixed Format
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View Replies!
View Related
Conditionally Format Each Group Of Rows And Each Column And Color The Cell In Each Column
I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows. 0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94 I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number. It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
View Replies!
View Related
VLookup Query: Seperate Sheet To Identify Entries That Have Today's Date In Column I And Then List Them In Worksheet 3
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3. Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
View Replies!
View Related
Date Based Format
I have a spreadsheet that I use to track my commissions on a monthly basis from about 8 sources. I have been tracking these monthly commissions from each source separately. In my spreadsheet I have a column that shows YTD total for each row(about 50 rows). Every month I have to go into each formula to update it to the corresponding month as follows: "=SUM(B3:J3)" then the next month I have to change it to "=SUM(B3:K3)" What I would like is if row "B-J" represents Jan.- Sept then on October first it will automatically sum the row "B-K"
View Replies!
View Related
Search Based On Format Value
I try create function, which will find based on format value. I mean f.e. when i have in cell function today() it show like 12.01.09. I need compare this date with date, which is create from other function. In my case is it =VALUE(CONCATENATE("1.";$D$2&".";D3)). So i want cell A1 (12.01.09 ) search in array (B1:B10), which dates are created from funcion =VALUE(CONCATENATE("1.";$D$2&".";D3)). There is 12.01.09 in B5. results will move cursor on cell with the same date if there is, or do nothing, if there is not the same date.
View Replies!
View Related
Lookup Based On Format
I am trying to read down a column of unknown rows stopping when it reaches a colored cell, calculate the results and return it to the colored cell "before" the read cells. Is this possible? For example: A3 is a colored cell that I need to return results of say A4:A6 A4:A6 have the data A5 is a colored cell that has results from say A6:A12 A6:A12 have the data etc... Now the data to calculate is from a Data Validation list that ignores Blanks accepted values: Blank, Yes, No and Maybe If ANY No, colored cell = No If ANY Maybe, colored cell = Maybe If ANY Blanks, leave colored cell blank If ALL Yes, colored cell = Yes "No" wins over any value "Maybe" wins over Yes and blank "Blank" wins over Yes It may be possible to simplify by breaking the "results" cell into another column but the above mentioned is their preferred way to handle and "automate" it. Also might be easier to put the results cell at the end of the read cells?
View Replies!
View Related
Format Cells Based On Their Value
I was wondering if anyone knows how to create a formula that is dependent on the colors of a range of cells subject to conditional formatting. For example, I would like something that could answer =if(A1 is red,"Phillies",if(A1 is blue,"Mets","Marlins)). However, the text color will be generated from conditional formatting, so most of the VBA code I have found thus far isn't working properly. Does anyone know of a solution?
View Replies!
View Related
Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.
I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in. I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
View Replies!
View Related
Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days 17, 4 17, 3 17, 5 26, 4 26, 8 26, 10 26, 7 On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View Replies!
View Related
Conditional Format Based On A Time
I have a row of cells pertaining to jobs B3 across to I3. In B3, C3, D3, E3 the value entered is text. In F3, G3, H3, I3 the value entered is time. What I am trying to do is to change the Background colour to Red on cells F3, G3, H3, I3 if either H3 or I3 or are empty at the end of the current day but only if there is some text in any or all of the cells B3 - E3 So for example if H3 or I3 have no time put in them by 23:59 today, when the workbook is opened tomorrow the cells background colour will turn red. Not sure if this if poossible or not? hopefully I have explained it well enough but if not pease let me know.
View Replies!
View Related
Conditional Format Based On Dates
I have a spreadsheet with due dates in column X. I want to create conditional formatting so that cells turn red when the due date is less than or equal to 30 days away, but turn gray when the due date has passed. I've accomplished the first step by using the following: =AND($X6
View Replies!
View Related
Return A Value Based On Cell Format
I don't even know if this is possible but can you use some formula within Excel to return a value to a cell based on the cell formatting of another cell? Here is specifically what I am trying to do: Cell A2 has a value of 10. If the font color of that cell is black (or "automatic") I want a formula in cell A1 that will return a value of "+10". If the font color of cell A2 is red, I want the formula in cell A1 to return a value of "-10".
View Replies!
View Related
Sumif Based On Currency Format
I have a list of sales made during a certain period. They are either in £ or $ and are in a list. Essentially like this (but with lots more info): sale1 £300 sale2 $450 sale3 £150 etc... What I would like to do is to have two cells at the top which sum only the £ values and a cell whcih sums only the $ values. Is this possible? My idea to was do a sumif formula based on the cell format of the sales value, but I can't see a way of doing this. The only other way I can see is to have a simple addition formula selecting only the certain cells i want, but this would be labour intensive to maintain, as the formula would need to be updated eachtime a new line is added.
View Replies!
View Related
|
TRACKER
